Exceljet

Quick, clean, and to the point

BMI calculation formula

Excel formula: BMI calculation formula
Generic formula 
=weight/height^2
Explanation 

This example shows one way to calculate BMI (Body Mass Index) in Excel. In the worksheet shown, we first convert height and weight to metric units, then use a standard metric formula to calculate BMI. The formula in H5 is:

=G5/F5^2

which returns calculated BMI based on a weight in G5 and height in F5.

What is BMI?

BMI stands for Body Mass Index. It is a simple (and inexpensive) way to assess body fat based on height and weight only. BMI is a screening tool that can be used to identify individuals who are underweight, overweight, or obese. However, BMI is not a diagnostic tool.

You can read more about BMI on the CDC website.

How this formula works

This example shows one way to calculate BMI (Body Mass Index) in Excel. The standard BMI formula is:

BMI = weight (kg) / height (m)2

The approach used here is to first convert height in inches and feet to meters, and weight in pounds to kilograms, then use the standard metric formula for BMI. This makes it easy to collect height and weight in commonly used units (in the United States), and also show the metric amounts used in the calculation.

The main challenge in this example is that most people in the United States still use the US customary measurement system to record height and weight, not the metric system. The first step therefore is to capture this information in commonly used units. This is done in columns B (feet) C (inches) and D (pounds).

Then, to calculate height in meters, we use the CONVERT function twice in cell F5:

=CONVERT(B5,"ft","m")+CONVERT(C5,"in","m")

The first CONVERT converts feet to meters:

=CONVERT(B5,"ft","m") // feet to meters

The second converts inches to meters:

=CONVERT(C5,"in","m") // inches to meters

Then the two values are simply added together to get a total height in meters.

To calculate weight in kilograms, we use CONVERT again in cell G5:

=CONVERT(D5,"lbm","kg") // pounds to kilograms

Finally, we are ready to apply the standard BMI formula. The formula in H5 is:

=G5/F5^2 // calculate BMI

To square height, we use Excel's operator for exponentiation, the caret (^). 

Alternatives

The formulas used above can be simplified somewhat. To calculate height, we can use a single CONVERT function like this:

=CONVERT(B5*12+C5,"in","m")

In other words, we convert feet to inches directly inside the function argument. When Excel evaluates the formula, this operation happens before the CONVERT function runs. Not quite as readable, but more compact.

Note: Excel's order of operations makes in unnecessary to wrap B5*12 in parentheses.

Similarly, we could convert inches to feet inside CONVERT like this:

=CONVERT(B5+C5/12,"ft","m")

The result is the same as above. The key point is that you are free to nest other calculations directly in a function argument.

As an alternative to the caret (^), the POWER function can be used to raise to a power like this:

= G5/POWER(F5,2)
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.