Microsoft Office Forums A formula to return value based on the sex, age and BMI of a person
 Register FAQ Search Today's Posts Mark Forums Read

#1
12-11-2018, 06:16 AM
 Marcia Windows 7 32bit Office 2007 Competent Performer Join Date: May 2018 Location: Philippines Posts: 231
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.
Attached Files
 BMI.xlsx (24.0 KB, 4 views)
#2
12-11-2018, 06:58 AM
 Debaser Windows 7 64bit Office 2010 32bit Competent Performer Join Date: Oct 2015 Posts: 172

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
12-11-2018, 08:44 AM
 Marcia Windows 7 32bit Office 2007 Competent Performer Join Date: May 2018 Location: Philippines Posts: 231

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
12-11-2018, 12:01 PM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 527

A simpler solution in attached workbook.
Attached Files
 BMI.xlsx (24.8 KB, 7 views)
#5
12-12-2018, 04:54 AM
 Marcia Windows 7 32bit Office 2007 Competent Performer Join Date: May 2018 Location: Philippines Posts: 231

Quote:
 Originally Posted by ArviLaanemets A simpler solution in attached workbook.

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
12-12-2018, 08:01 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 527

Quote:
 Originally Posted by Marcia ...and the table ran to 2800 rows (age 48 months to 228 months or 5 to 19 years old)
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 Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Highlander01 Excel 7 06-02-2017 01:17 PM kwilson307 Excel 1 04-20-2017 05:07 AM george batshon Excel 2 01-28-2017 05:11 AM prihia971 Excel 1 10-06-2016 02:17 AM Helmszee Mail Merge 1 04-12-2016 02:30 AM

All times are GMT -7. The time now is 07:33 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top