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:
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.
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:
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.
To convert a measurement in feet and inches to inches only (i.e. 4'5" to 53) you can use a formula based on several functions, including LEFT, FIND, MID, and SUBSTITUTE. In the example shown, the formula in D5 is: = LEFT ( B5 , FIND ( "'...
The Excel CONVERT function converts a number in one measurement system to another. For example, you can use CONVERT to convert feet into meters, pounds into kilograms, Fahrenheit to Celsius, gallons into liters, and for many other unit...
The Excel POWER function returns a number to a given power. The POWER function works like an exponent in a standard math equation.
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.