BMI or Body Mass Index is a ‘rule of thumb’ calculating body fat based on height and weight. It’s easy to calculate in Excel whether you use metric or feet/pounds.
BMI can be a polite way of saying how overweight a person is! A BMI over 30 is considered obese and need of work to bring down weight. More on BMI below.
You can easily calculate your own BMI in Excel.
Body Mass Index – BMI – Excel formula
The BMI formula for metric measures is:
weight (kg) / height (m)2
Weight in Kilos divided by the square of height in metres.
The formula in Excel is also simple:
=weight/height^2
The ^ means ‘raise to the power’.
BMI in Feet, Inches and Pounds
The US/Imperial measure world needs a little more work, which Excel also handles easily with the CONVERT Function.
Excel has a Convert function to change one measurement system to another.
Syntax
=CONVERT(number, from_unit, to_unit)
- Number – Numeric value you require to convert.
- from_unit – Starting units.
- to_unit – Ending units.
Converting Pounds to Kilograms
You can easily convert pounds to kilograms in Excel. Based on the above example, the formula is:
=CONVERT(B4,"lbm","kg")
It’s ‘lbm’ for pounds, not ‘lb’.
Converting Feet and Inches into Metres
Feet and Inches can be converted into metres in Excel. Based on the above example, the formula is:
=CONVERT(C4,"ft","m")+CONVERT(D4,"in","m")
BMI in Excel for Feet and Pounds
A single formula for using feet with inches /pounds in a BMI calculation looks like this:
B4 is weight in pounds
C4 is height part in feet
D4 is height part in inches
=CONVERT(B4,"lbm","kg")/ (CONVERT(C4,"ft","m")+CONVERT(D4,"in","m"))^2
The brackets are necessary because Excel’s order of precedence will do ‘raising to a power’ or squaring first. Without the brackets, Excel would square the ‘inches to metres’ Convert() only.
BMI from pounds and inches only
If the height is given in Inches only (e.g. 69 inches), the formula is a little simpler.
=CONVERT(B4,"lbm","kg")/ CONVERT(D4,"in","m")^2
Another formula for BMI from pounds and inches
There is another way to get a BMI from pounds and inches:
(Weight in Pounds / Height in inches2 ) x 703
=(Weight / Height^2) * 703
Lambda option for BMI
If there are a lot of BMI calculations in your worksheet, especially the conversion ones, you could make your own BMI() formula using Lambda()
Lambda( weight, height, weight/height^2 )
In US measures there are three parameters for weight, height in feet and height in inches:
Lambda( wgtlbm, hgtFt, hgtIn,
CONVERT(wgtlbm,"lbm","kg")/ (CONVERT(hgtFt,"ft","m")+CONVERT(hgtIn,"in","m")^2)) )
Conditional Formatting
As you can see above, some conditional formatting can highlight results outside the healthy range with coloured cells or other formatting tricks.
See BMI calcs can do more in Excel
About BMI – Body Mass Index
BMI is a broad screening tool for determining if an adult is obese, overweight, or underweight.
Underweight – under 18.5
Healthy – 18.5 to 24.9
Overweight – 25 to 29.9
Obese – over 30 BMI
The BMI needs to be treated with care because it doesn’t apply to everyone.
The British NHS has some good advice about BMI
“ Muscle is much denser than fat, so very muscular people, such as heavyweight boxers, weight trainers and athletes, may be a healthy weight even though their BMI is classed as obese.
Your ethnic group can also affect your risk of some health conditions. For example, adults of South Asian origin may have a higher risk of some health problems, such as diabetes, with a BMI of 23, which is usually considered healthy. “
They also note, though it hardly needs mentioning, that the BMI isn’t relevant for pregnant women, children or teenagers.
BMI calcs can do more in Excel
Excel’s CONVERT function for metric and more
Full CONVERT() measurement list for Excel
50 first dates with Excel
Visa stay calculator for European visitors – with Excel
Excel’s order of calculation and BODMAS
