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
Expert
A formula to return value based on the sex, age and BMI of a person
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
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, 10 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
Location: UK
Posts: 221
Debaser will become famous soon enough
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
Expert
A formula to return value based on the sex, age and BMI of a person
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
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: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

A simpler solution in attached workbook.
Attached Files
File Type: xlsx BMI.xlsx (24.8 KB, 13 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
Expert
A formula to return value based on the sex, age and BMI of a person
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
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: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:32 PM.


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