#1
|
||||
|
||||
A formula to return value based on the sex, age and BMI of a person
Hi, I have tables of Body Mass Index (BMI) for boys and Girls containing matrices of BMI categories (Normal, Overweight...). On a separate table is a list of names with corresponding information of sex, age and BMI. Based on the data provided in cells A1415, please help me with a formula to return the category if a person is severely wasted, wasted, normal, overweight or obese in E14 and E15. Thank you. Sheet attached. |
#2
|
||||
|
||||
Unfortunate data layout, but here's one way: in E14:
=IF(B14="F",IF(COUNTIF(D14,INDEX(Severely_Wasted,M ATCH(C14,Month,0))),"Severely wasted",IF(COUNTIF(D14,INDEX(Obese,MATCH(C14,Month ,0))),"Obese",CHOOSE(ROUND(MATCH(D13,INDEX(tBMIGir ls[[From]:[To3]],MATCH(C14,Month,0),0))/2,0),"Wasted","Normal","Overweight"))),IF(COUNTIF( D14,INDEX(tBMIBoys[[Severely Wasted if BMI is ]],MATCH(C14,tBMIBoys[Age in Months],0))),"Severely wasted",IF(COUNTIF(D14,INDEX(tBMIBoys[[Obese ]],MATCH(C14,tBMIBoys[Age in Months],0))),"Obese",CHOOSE(ROUND(MATCH(D14,INDEX(tBMIBoy s[[From]:[To3]],MATCH(C14,tBMIBoys[Age in Months],0),0))/2,0),"Wasted","Normal","Overweight")))) and copy down. |
#3
|
||||
|
||||
Thank you Debaser. It took me sometime to reply because when I copied the formula and edited it to fit into my actual data, a lot of N/A came out, caused by children below 72 months of age. I did not notice that there is a separate growth table for infants to toddlers. I will try adding another IFs in the formula that you gave. I hope I'll get it right.
|
#4
|
|||
|
|||
A simpler solution in attached workbook.
|
#5
|
||||
|
||||
I'm done completing the regrouping of the BMI data per age in months and per category and the table ran to 2800 rows (age 48 months to 228 months or 5 to 19 years old) but it is a lot simpler than the formula in post #2. Again, thank you. |
#6
|
|||
|
|||
I myself too spent most of time editing BMI table for example. The actual formula did take a couple of minutes
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula to return a value of 1,2,3 or based on a set criteria | Highlander01 | Excel | 7 | 06-02-2017 01:17 PM |
Return Max Text or Number Value based on Criteria | kwilson307 | Excel | 1 | 04-20-2017 05:07 AM |
Index to return value based on 2 conditions | george batshon | Excel | 2 | 01-28-2017 05:11 AM |
Help with a formula to return a zero | prihia971 | Excel | 1 | 10-06-2016 02:17 AM |
Return List Based On Specific Value | Helmszee | Mail Merge | 1 | 04-12-2016 02:30 AM |