Calculate ideal body weight (IBW) based on multiple conditions

Hello everyone. I am new to this website and forum, and I am very impressed from the professional replies and help that members of the forum provide.
I need to calculate the ideal body weight based on multiple conditions (i.e. various age group categories and body mass index (BMI). The following are the conditions (I am using MS Office 10):

For ages between 18-64:
• If age >=18.5 but <=64, and body mass index (BMI)<18.5, then calculate the ideal body weight (IBW) as 20*(height (cm)/100)^2
• If age >=18.5 but <=64, and body mass index (BMI)>=18.5 but <=24.9, then calculate the ideal body weight (IBW) as BMI value*(height (cm)/100)^2
• If age >=18.5 but <=64, and body mass index (BMI)>=25 but <=30, then calculate the ideal body weight (IBW) as 22*(height (cm)/100)^2
• If age >=18.5 but <=64, and body mass index (BMI)>30, then calculate the ideal body weight (IBW) as 27*(height (cm)/100)^2

For ages between >65:
• If age >=65, and body mass index (BMI)<25, then calculate the ideal body weight (IBW) as 25*(height (cm)/100)^2
• If age >=65, and body mass index (BMI)>=25 but <=30, then calculate the ideal body weight (IBW) as BMI value*(height (cm)/100)^2
• If age >=65, and body mass index (BMI)>30, then calculate the ideal body weight (IBW) as 30*(height (cm)/100)^2

The excel sheet I am using has other formula to calculate BMI from body weight (in kg) and height (in meter). The resulting BMIis then be the input in my formula to calculate IBW. The formula either yields the message (you have entered too many arguments for this function, or sometimes when I use different age (e.g. 88), the formula yields “False”. Sometimes if the formula also gives a “False” for different BMI results. Please help.

This is the formula I am using to calculate ideal body weight (IBW):
=IF(AND(B3>=18,B3<=64),IF(D3<18.5,20*(B5/100)^2,IF(AND(D3>=18.5,D3<=24.9),D3*(B5/100)^2,IF(D3>=25,D3<=30,22*(B5/100)^2,IF(D3>30,27*(B5/100)^2)),IF(B3>65,IF(D3<25,25*(B5/100)^2,IF(D3>=25,D3<=30,D3*(B5/100)^2,IF(D3>30,30*(B5/100)^2)))))))

Please see attachment for the sheet I am working on.
Thank you,
Sam

Hi Sam,

Here is the formula I came up with and it appears to be working when I put in a bunch of different values. You can give it a try and see if it helps.

Please Login or Register  to view this content.

Thanks,

Dan

Thank you Dan. I tried it and so far seems to be working perfectly. Thank you so much..you saved my neck.

Have a good day.

sam