Metric & Imperial calculations for BMI in Excel - Help!

**jehlie** · 06-20-2012, 02:13 PM

Hello everyone, I will try to explain this as best as I can!

I have been asked to design this model in Excel for school; and it is all about health and fitness. One of the specifications is to include a BMI formula, my teacher said you don't have to include metric AND imperial, just one of the two. But, I want to try and go one step further and try to include them both anyways and make the model a bit more complicated. Obviously, I have done my research for the metric and imperial formulas.

Metric: Weight in kg/height in meters squared.

Imperial: (Weight in pounds/height in inches squared) x 703

I'm just having a bit of trouble implementing these formulas into my model, and was wondering if anyone could help. Obviously, BMI is calculated by dividing weight by height, but my formulae seems to give me crazy BMI results, well over 100. I am using an "IF" formula so I can have true and false values, and I have created a drop down list next to my weight and height cells so the user can switch between measurements. This is my formula for my BMI:

=IF(D8="pounds",C8/C10*2*703,C8/C10*2)

*D8 is the cell which contains a drop down list for kg and pounds*

Breakdown of the formula:
If the user chooses pounds in cell D8, then it should calculate in imperial measurements (pounds). C8 is my weight cell and C10 is my height, they divide and C10 is multiplied by 2 (as it is squared) and then multiplied again by 703 (which I'm a bit hesitant about). That is my true statement. The comma ends that part of the formula and I now move onto my false statement. So, if the user does not switch to pounds it should calculate in metric measurements (kg). So again, C8 is divided by C10 multiplied by two, because it is squared, which should give the right BMI, but does not. I'm not exactly sure where I am going wrong.

I have another drop down list next to my height cell which I don't use in my formula, I'm wondering if I need to use that in any way?

When I try the pounds part of the formula it gives me a crazy BMI like 1000+ when I enter 100 pounds and 105 inches, when a normal BMI should be around 25. Which is why I am unsure if the multiply by 703 part in the formula is correct.

Here are some screenshots of the model and formulae that will maybe help:
(Apologies they had to be uploaded to a site, it said my extensions were incorrect to upload directly to here)

BMI formula: http://imageshack.us/f/42/screentr.png/

Kg/pounds drop down list: http://img580.imageshack.us/img580/369/screen2tu.png

Meters/inches drop down list: http://img59.imageshack.us/img59/6966/screen3hce.png

Imperial (Pounds) BMI result: http://img191.imageshack.us/img191/382/screen4ks.png

Metric (kg) BMI result: http://img339.imageshack.us/img339/1680/screen5kc.png

Any help with the formula to get a reliable BMI result will be greatly appreciated, I apologise for making this thread look like a 10 page essay, but it was hard to explain! I also thank you for your patience if you managed to read right the way through this - thank you.
Please help me?!

-Jehlie

**ben_hensel** · 06-20-2012, 02:17 PM

To be pedantic, grams (and kilograms) are units of mass, not weight.

Also, I noticed you used this formula:
=IF(D8="pounds",C8/C10*2*703,C8/C10*2)

But you probably want this formula:
=IF(D8="pounds",C8/C10^2*703,C8/C10^2)

You're not using the correct operand, I believe.

EDIT:

I banged out a BMI calculator, compare it to yours.

**jehlie** · 06-20-2012, 03:01 PM

Thanks for the help. I just changed the formula and the pounds part is giving me way more reliable results. The kg's is still a bit weird though, I might just be entering in weird values though. Anyways I would try out your calculator but I am currently on a Mac and do not have Excel. My model is on my school computer and I have a remote connection going to it in order to access my model. The school has also blocked web mail so I can't send it to myself and open it on my school computer. However, I could open it as an image on my Mac and I entered the same imperial measurements you got and I got pretty much the exact same results.

If you wouldn't mind, maybe you could enter in another set of values but this time for metric measurements, then re-attach the updated file with the metric values so I can see the image of it and try the same values in my model? Or, you could just post what values you entered in manually on here and then resulting BMI.

Thank you for your time, much appreciated.
-Jehlie