16 diffetent if functions for 2 criteria

[Andy]

I am using excel to calculate BMI for children. I have 16 different if
functions entered and want to set a criteria for using each function.
Basically I want to use one formula for 5 year old boys, another for 5 yr.
old girls, one for 6 year old boys, and another for 6 year old girls, etc. I
have the formulas in a different sheet. I've tried countifs but I can't seem
to get that to work. Example below:
A B C D E
F G
Name Gender Age Height Weight BMI Category

 

[Jacob Skaria]

Andy

Instead of 16 different if functions..you can have a table such as below in
Sheet2. and use a VLOOKUP() MATCH() formula to get the desired results

In Sheet1 try the below formula
=VLOOKUP(age,Sheet2!A1:C10,MATCH(boy/girl,Sheet2!A1:C1,0),0)

OR
=VLOOKUP(5,Sheet2!$A$1:$C$10,MATCH("Boy",Sheet2!$A$1:$C$1,0),0)
which will lookup Boy with Age5 and return the corresponding value from the
Sheet2 table. for the above formula it will return x1 or what ever in that
cell...

Col A Col B Col C
Age Boy Girl
5 x1 y1
6 x2 y2
7 x3 y3
8 x4 y4
9 x5 y5
10 x6 y6

If this post helps click Yes

 

[Andy]

Can't get that to work... the result is a formula. Maybe this will better
explain what I need. If Gender Column is "M" and age column is "5" then I
need the result of the formulaA. If Gender Column is "M" and age column is
"6" then I need the result of formulaB, etc.

In sheet 2 I have a table that has the corresponding formula for gender and
age similiar to the one as follows:

Gender Column Age Column Formula
M 5
=IF(M2>27.9,"Overweight",IF(M2>23...
M 6
=IF(M2>28,"Overweight",IF(M2>23...
M 7
=IF(M2>29,"Overweight",IF(M2>23...
M 8
=IF(M2>27.9,"Overweight",IF(M2>23...
F 5
=IF(M2>28,"Overweight",IF(M2>23...
F 6
=IF(M2>29,"Overweight",IF(M2>23...
F 7
=IF(M2>30,"Overweight",IF(M2>23...

 

[Jacob Skaria]

Post your conditions; there is much better way to do this.

You can have a solution with the current arrangment using VBA..

If this post helps click Yes

 

[Andy]

Hope this makes sense...

Gender Age Formula
F 5
=IF(I2>27.9,"Overweight**",IF(I2>23,"Overweight*",IF(I2>18.1,"Overweight",IF(I2>16.8,"At Risk of Overweight",IF(I2>13.4,"Normal Range","Underweight")))))

F 6
=IF(I3>27.9,"Overweight**",IF(I3>23.4,"Overweight*",IF(I3>18.7,"Overweight",IF(I3>17.1,"At Risk of Overweight",IF(I3>13.3,"Normal Range","Underweight")))))

F 7
=IF(I4>28.9,"Overweight**",IF(I4>24,"Overweight*",IF(I4>19.5,"Overweight",IF(I4>17.6,"At Risk of Overweight",IF(I4>13.3,"Normal Range","Underweight")))))

F 8
=IF(I5>29.9,"Overweight**",IF(I5>25,"Overweight*",IF(I5>20.5,"Overweight",IF(I5>18.3,"At Risk of Overweight",IF(I5>13.5,"Normal Range","Underweight")))))

F 9
=IF(I6>29.9,"Overweight**",IF(I6>25,"Overweight*",IF(I6>21.7,"Overweight",IF(I6>19.1,"At Risk of Overweight",IF(I6>13.7,"Normal Range","Underweight")))))

F 10
=IF(I7>29.9,"Overweight**",IF(I7>26,"Overweight*",IF(I7>22.9,"Overweight",IF(I7>19.9,"At Risk of Overweight",IF(I7>13.9,"Normal Range","Underweight")))))

F 11
=IF(I8>29.9,"Overweight**",IF(I8>27,"Overweight*",IF(I8>23.9,"Overweight",IF(I8>20.8,"At Risk of Overweight",IF(I8>14.3,"Normal Range","Underweight")))))

F 12
=IF(I9>29.9,"Overweight**",IF(I9>27,"Overweight*",IF(I9>25.1,"Overweight",IF(I9>21.7,"At Risk of Overweight",IF(I9>14.7,"Normal Range","Underweight")))))

[gasp for air ]

M 5
=IF(I2>27.9,"Overweight**",IF(I2>23,"Overweight*",IF(I2>17.9,"Overweight",IF(I2>16.8,"At Risk of Overweight",IF(I2>13.7,"Normal Range","Underweight")))))

M 6
=IF(I3>27.9,"Overweight**",IF(I3>23.4,"Overweight*",IF(I3>18.3,"Overweight",IF(I3>17,"At Risk of Overweight",IF(I3>13.7,"Normal Range","Underweight")))))

M 7
=IF(I4>28.9,"Overweight**",IF(I4>24,"Overweight*",IF(I4>19,"Overweight",IF(I4>17.4,"At Risk of Overweight",IF(I4>13.7,"Normal Range","Underweight")))))

M 8
=IF(I5>29.9,"Overweight**",IF(I5>25,"Overweight*",IF(I5>19.9,"Overweight",IF(I5>17.9,"At Risk of Overweight",IF(I5>13.7,"Normal Range","Underweight")))))

M 9
=IF(I6>29.9,"Overweight**",IF(I6>25,"Overweight*",IF(I6>21,"Overweight",IF(I6>18.6,"At Risk of Overweight",IF(I6>13.9,"Normal Range","Underweight")))))

M 10
=IF(I7>29.9,"Overweight**",IF(I7>26,"Overweight*",IF(I7>22,"Overweight",IF(I7>19.3,"At Risk of Overweight",IF(I7>14.1,"Normal Range","Underweight")))))

M 11
=IF(I8>29.9,"Overweight**",IF(I8>27,"Overweight*",IF(I8>23.1,"Overweight",IF(I8>20.1,"At Risk of Overweight",IF(I8>14.5,"Normal Range","Underweight")))))

M 12
=IF(I9>29.9,"Overweight**",IF(I9>27,"Overweight*",IF(I9>24.1,"Overweight",IF(I9>21,"At Risk of Overweight",IF(I9>14.9,"Normal Range","Underweight")))))

Whew... I'm not sure if if matters, but column I in the formula is their BMI
which is calulated from a formula. Age is also calculated using a datediff
formula. Thanks for your help!!!!!!!