| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
16 diffetent if functions for 2 criteria
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
16 diffetent if functions for 2 criteria
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 --------------- Jacob Skaria "Andy" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
16 diffetent if functions for 2 criteria
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(M227.9,"Overweight",IF(M223... M 6 =IF(M228,"Overweight",IF(M223... M 7 =IF(M229,"Overweight",IF(M223... M 8 =IF(M227.9,"Overweight",IF(M223... F 5 =IF(M228,"Overweight",IF(M223... F 6 =IF(M229,"Overweight",IF(M223... F 7 =IF(M230,"Overweight",IF(M223... "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Andy" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
16 diffetent if functions for 2 criteria
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 --------------- Jacob Skaria "Andy" wrote: 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(M227.9,"Overweight",IF(M223... M 6 =IF(M228,"Overweight",IF(M223... M 7 =IF(M229,"Overweight",IF(M223... M 8 =IF(M227.9,"Overweight",IF(M223... F 5 =IF(M228,"Overweight",IF(M223... F 6 =IF(M229,"Overweight",IF(M223... F 7 =IF(M230,"Overweight",IF(M223... "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Andy" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
16 diffetent if functions for 2 criteria
Hope this makes sense...
Gender Age Formula F 5 =IF(I227.9,"Overweight**",IF(I223,"Overweight*", IF(I218.1,"Overweight",IF(I216.8,"At Risk of Overweight",IF(I213.4,"Normal Range","Underweight"))))) F 6 =IF(I327.9,"Overweight**",IF(I323.4,"Overweight* ",IF(I318.7,"Overweight",IF(I317.1,"At Risk of Overweight",IF(I313.3,"Normal Range","Underweight"))))) F 7 =IF(I428.9,"Overweight**",IF(I424,"Overweight*", IF(I419.5,"Overweight",IF(I417.6,"At Risk of Overweight",IF(I413.3,"Normal Range","Underweight"))))) F 8 =IF(I529.9,"Overweight**",IF(I525,"Overweight*", IF(I520.5,"Overweight",IF(I518.3,"At Risk of Overweight",IF(I513.5,"Normal Range","Underweight"))))) F 9 =IF(I629.9,"Overweight**",IF(I625,"Overweight*", IF(I621.7,"Overweight",IF(I619.1,"At Risk of Overweight",IF(I613.7,"Normal Range","Underweight"))))) F 10 =IF(I729.9,"Overweight**",IF(I726,"Overweight*", IF(I722.9,"Overweight",IF(I719.9,"At Risk of Overweight",IF(I713.9,"Normal Range","Underweight"))))) F 11 =IF(I829.9,"Overweight**",IF(I827,"Overweight*", IF(I823.9,"Overweight",IF(I820.8,"At Risk of Overweight",IF(I814.3,"Normal Range","Underweight"))))) F 12 =IF(I929.9,"Overweight**",IF(I927,"Overweight*", IF(I925.1,"Overweight",IF(I921.7,"At Risk of Overweight",IF(I914.7,"Normal Range","Underweight"))))) [gasp for air :)] M 5 =IF(I227.9,"Overweight**",IF(I223,"Overweight*", IF(I217.9,"Overweight",IF(I216.8,"At Risk of Overweight",IF(I213.7,"Normal Range","Underweight"))))) M 6 =IF(I327.9,"Overweight**",IF(I323.4,"Overweight* ",IF(I318.3,"Overweight",IF(I317,"At Risk of Overweight",IF(I313.7,"Normal Range","Underweight"))))) M 7 =IF(I428.9,"Overweight**",IF(I424,"Overweight*", IF(I419,"Overweight",IF(I417.4,"At Risk of Overweight",IF(I413.7,"Normal Range","Underweight"))))) M 8 =IF(I529.9,"Overweight**",IF(I525,"Overweight*", IF(I519.9,"Overweight",IF(I517.9,"At Risk of Overweight",IF(I513.7,"Normal Range","Underweight"))))) M 9 =IF(I629.9,"Overweight**",IF(I625,"Overweight*", IF(I621,"Overweight",IF(I618.6,"At Risk of Overweight",IF(I613.9,"Normal Range","Underweight"))))) M 10 =IF(I729.9,"Overweight**",IF(I726,"Overweight*", IF(I722,"Overweight",IF(I719.3,"At Risk of Overweight",IF(I714.1,"Normal Range","Underweight"))))) M 11 =IF(I829.9,"Overweight**",IF(I827,"Overweight*", IF(I823.1,"Overweight",IF(I820.1,"At Risk of Overweight",IF(I814.5,"Normal Range","Underweight"))))) M 12 =IF(I929.9,"Overweight**",IF(I927,"Overweight*", IF(I924.1,"Overweight",IF(I921,"At Risk of Overweight",IF(I914.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!!!!!!! "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Andy" wrote: 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(M227.9,"Overweight",IF(M223... M 6 =IF(M228,"Overweight",IF(M223... M 7 =IF(M229,"Overweight",IF(M223... M 8 =IF(M227.9,"Overweight",IF(M223... F 5 =IF(M228,"Overweight",IF(M223... F 6 =IF(M229,"Overweight",IF(M223... F 7 =IF(M230,"Overweight",IF(M223... "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Andy" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| database functions and criteria | Excel Worksheet Functions | |||
| Using wildcards in criteria for sumifs functions | Excel Discussion (Misc queries) | |||
| SUM Functions with multiple criteria | Excel Discussion (Misc queries) | |||
| Non-consecutive criteria database functions | Excel Worksheet Functions | |||
| Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Linear Mode
