Using IF function .....

[porky2j]

Really sorry if I have posted this in the wrong group (I can't seem to
post in the worksheetfunctions group.

I am currently using the IF function to display a score value based on
age. I am currently using 2 cells with 2 seperate equations for boys
(with age in C5):

=IF($C$5=11,10.6,IF($C$5=12,12.8,IF($C$5=13,15,IF($C$5=14,17.2,IF($C
$5=15,19.3,IF($C$5=16,20.1,IF($C$5=17,20.9,IF($C$5=18,21.6))))))))

and girls (with age in B5):

=IF($B$5=11,9,IF($B$5=12,10.6,IF($B$5=13,12.1,IF($B$5=14,13.7,IF($B
$5=15,15.4,IF($B$5=16,15.8,IF($B$5=17,16.3,IF($B$5=18,16.7))))))))

I am trying to combine the 2 equations so that it will display based
on entering age in C5 and M or F (for sex) in B5.

I am pretty sure that I need to be using the AND / OR functions but
can't seem to crack it. Any help (as always) much appreciated!

Thanks

 

[Don Guillett]

One way would be yourformula*(if(b5="f"),.78,1)

 

[edtheant]

It would be much easier to manage and also more scalable to use a VLOOKUP()
function.

On a separate sheet create a table with:

_________ A________B________ C________D____
1| 11 M =A1&B1 10.6
2| 12 M =A2&B2 12.8
3| 13 M =A3&B3 15
4| 14 M =A4&B4 17.2
5| 11 F =A5&B5 9
6| 12 F =A6&B6 10.6
7| 13 F =A7&B7 12.1
8| 14 F =A8&B8 13.7

etc...


Then the formula would be =vlookup($c5&$b5,sheet2!$C:$D,2,0)