IF Function .....

[porky2j]

Very sorry - I have posted this in another groupd and have realised
this is in fact the correct place to ask.

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 into one, so that the user can
enter age and sex (m or f) and the output will be the appropriate
score.

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

 

[Pete_UK]

Rather than have one complex formula to cover boys and girls together,
put those ages and scores in a table somewhere (eg X1:Z8), like this:

11 10.6 9
12 12.8 10.6
13 15 12.1
14 17.2 13.7
15 19.3 15.4
16 20.1 15.8
17 20.9 16.3
18 21.6 16.7

Then if you use B5 for the gender and C5 for the age, you can have a
formula like this:

=IF(B5="m",VLOOKUP(C5,X1:Y8,2),IF(B5="f",VLOOKUP(C5,X1:Z8,3),""))

Hope this helps.

Pete

 

[porky2j]

AH!! Perfect - Thanks!!!!!

 

[Pete_UK]

You're welcome - thanks for taking the trouble to feed back.

Pete