Creating my own BMR (basal metabolic rate) Calculator

[ramghai]

I want to create my own basal metabolic rate (BMR) calculator in Excel 2010 using the Harris Benedict Formula

I want it to be in imperial and metric, male and female

I want the BMR to be calculated in C9
The age is in C3 - I have used data validation in that cell
The gender is in C4 - I have used a drop down list for Male or Female
The weight is in C5 - The user can input data in kg or st,lb, using the drop down menu
The heigh is in C6 - Users can input data in m or ft,in using the drop down menu

In cell C8, I have the
I want the BMR to be calculated in C9

Any help would be much appreciated!

 

[ramghai]

Imperial (ft, in/st, lb)
Women - BMR = 655 + ( 4.35 x weight in pounds ) + ( 4.7 x height in inches ) - ( 4.7 x age in years )
Men - BMR = 66 + ( 6.23 x weight in pounds ) + ( 12.7 x height in inches ) - ( 6.8 x age in year )


Metric (kg/m)
Women - BMR = 655 + ( 9.6 x weight in kilos ) + ( 1.8 x height in cm ) - ( 4.7 x age in years )
Men - BMR = 66 + ( 13.7 x weight in kilos ) + ( 5 x height in cm ) - ( 6.8 x age in years )

This is the Harris Benedict formula

 

[p45cal]

what was it?

msg #15

 

[ramghai]

msg #15

oh...thanks for the formula, but the results werent quiet right. I compared the results with some online BMR calculators and they was different.

 

[p45cal]

oh...thanks for the formula, but the results werent quiet right. I compared the results with some online BMR calculators and they was different.

Yes, I made an error of not converting the metres to cms, so here is the corrected formula:

=IF(C4="Male",66.473,655.0955)+IF(C4="Male",13.7516,9.5634)*IF(D5="st",(C5*14+E5)*0.45359237,C5)+IF(C4="Male",5.0033,1.8496)*IF(D6="ft",(C6*12+E6)*2.54,C6*100)-IF(C4="Male",6.755,4.6756)*C3

I cited my source for the formulae, and double checking these they are correct as on page 373 of
A Biometric Study of Human Basal Metabolism
J. Arthur Harris and Francis G. Benedict
Proc Natl Acad Sci U S A. 1918 December
viewable here:
http://www.ncbi.nlm.nih.gov/pmc/articles/PMC1091498/?page=4

There is no reason in Excel to have 4 approximations of these formulae, which your 4 formulae are; You would expect a person's BMR to be the same whether you measured them in imperial units or metric units, however your formulae give different results for the same person:
A female, 76kgs, 1.83 metres, aged 54:
1460.2
Same female, 11st 13.55lbs, 6ft 0.0472ins:
1468.664
a 0.6% difference

Using the original formula values (as I have in D9) the two results were
1467.9083 and 1467.9024, a 0.0004% difference.

If we use a male with the same height, weight and age as the female above:
Your metric formula gives 1655, your imperial formula gives 1657.636, a better 0.16% difference, while original formula values (D9 again) give 1662.420(imp) and 1662.428(metric), a 0.0005% difference.

 

[ramghai]

ThankYou very much for your time and help

Thany you to everyone to helped me

IT WORKS!!