Easily Calculate Body Mass Index in Excel

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.

The BMI formula 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/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.

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

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

Don't miss out!
Join Office for Mere Mortals

Office for Mere Mortals is where thousands pick up useful tips and tricks for Word, Excel, PowerPoint and Outlook.

Invalid email address
Give it a try. You can unsubscribe at any time.  We've never spammed or sold addresses since we started over twenty years ago.