Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-11-2018, 06:16 AM
Marcia's Avatar
Marcia Marcia is offline A formula to return value based on the sex, age and BMI of a person Windows 7 32bit A formula to return value based on the sex, age and BMI of a person Office 2007
Competent Performer
A formula to return value based on the sex, age and BMI of a person
 
Join Date: May 2018
Location: Philippines
Posts: 231
Marcia is on a distinguished road
Default 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
File Type: xlsx BMI.xlsx (24.0 KB, 4 views)
Reply With Quote
  #2  
Old 12-11-2018, 06:58 AM
Debaser's Avatar
Debaser Debaser is offline A formula to return value based on the sex, age and BMI of a person Windows 7 64bit A formula to return value based on the sex, age and BMI of a person Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 172
Debaser is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 12-11-2018, 08:44 AM
Marcia's Avatar
Marcia Marcia is offline A formula to return value based on the sex, age and BMI of a person Windows 7 32bit A formula to return value based on the sex, age and BMI of a person Office 2007
Competent Performer
A formula to return value based on the sex, age and BMI of a person
 
Join Date: May 2018
Location: Philippines
Posts: 231
Marcia is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 12-11-2018, 12:01 PM
ArviLaanemets ArviLaanemets is offline A formula to return value based on the sex, age and BMI of a person Windows 8 A formula to return value based on the sex, age and BMI of a person Office 2016
Expert
 
Join Date: May 2017
Posts: 527
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

A simpler solution in attached workbook.
Attached Files
File Type: xlsx BMI.xlsx (24.8 KB, 7 views)
Reply With Quote
  #5  
Old 12-12-2018, 04:54 AM
Marcia's Avatar
Marcia Marcia is offline A formula to return value based on the sex, age and BMI of a person Windows 7 32bit A formula to return value based on the sex, age and BMI of a person Office 2007
Competent Performer
A formula to return value based on the sex, age and BMI of a person
 
Join Date: May 2018
Location: Philippines
Posts: 231
Marcia is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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.
Reply With Quote
  #6  
Old 12-12-2018, 08:01 AM
ArviLaanemets ArviLaanemets is offline A formula to return value based on the sex, age and BMI of a person Windows 8 A formula to return value based on the sex, age and BMI of a person Office 2016
Expert
 
Join Date: May 2017
Posts: 527
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

Quote:
Originally Posted by Marcia View Post
...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
Reply With Quote
Reply

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
A formula to return value based on the sex, age and BMI of a person 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
A formula to return value based on the sex, age and BMI of a person Help with a formula to return a zero prihia971 Excel 1 10-06-2016 02:17 AM
A formula to return value based on the sex, age and BMI of a person Return List Based On Specific Value Helmszee Mail Merge 1 04-12-2016 02:30 AM


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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft