How to Calculate BMI with Formula in Excel: 3 Methods

Method 1 – Calculate BMI in Metric Unit

1. Select cell E5 and type the following formula: =D5/C5^2
   D5 is the weight, and C5 is a person’s height. Both quantities are given in metric units.
2. Press Enter to get the BMI.
3. Drag down the Fill Handle to get BMI for other persons.
4. To calculate Body Status (if a person is average, overweight, or obese), you can use the following formula: =IF(E5<=18.5,"Underweight",IF(E5<=24.9,"Normal",IF(E5<=29.9,"Overweight","Obesity")))
   The IF function returns a body status based on the cell value of E5. If E5<=18.5, it returns ‘Underweight’. If E5 is between 18.5 and 24.9, it returns ‘Normal’. If E5 is between 24.9 and 29.9, it returns ‘Overweight’. If it is greater than 29.9, it returns ‘Obesity’.
5. Press Enter and drag down the Fill Handle to get body status for individuals.

Method 2 _  Calculate BMI in Imperial Unit

2.1 BMI from Inches and Pounds

If the height is given in inches and weight in pounds, you need to adjust the basic arithmetic formula with a factor. To calculate BMI from inches and pounds:

1. Select Cell E5 and type the following formula: (=D5/C5^2)*703
   D5 is the weight, and C5 is a person’s height. The constant 703 is used to adjust the units of measurement.
2. Press Enter and drag down the Fill Handle to get results in the other cells.
3. Use the following formula in Cell F5 to get the body status of an individual: =IF(E5<=18.5,"Underweight",IF(E5<=24.9,"Normal",IF(E5<=29.9,"Overweight","Obesity")))

You will get the BMI and Body Status of different people in Excel.

Alternative Method: Use of CONVERT Function

1. Create two helper columns: one to store the heights in the meter unit and another one to store the weights in the kilogram unit. Columns E and F are our helper columns.
2. Select cell E5 and type the following formula: =CONVERT(C5,"in","m")
   This formula converts the value of cell C5 from inches to meter units.
3. To convert weight from pounds to kilograms, use the following formula in Cell F5: =CONVERT(D5,"lbm","kg")
4. After the conversion, select Cell G5 and type the following formula: =F5/E5^2
5. Press Enter and drag down the Fill Handle to BMI for all individuals.

2.2 BMI from Feet, Inches and Pounds

1. Select Cell F5 and type the following formula: =CONVERT(E5,"lbm","kg")/(CONVERT(C5,"ft","m")+CONVERT(D5,"in","m"))^2
   The CONVERT(E5,”lbm”,”kg”) part converts pounds to kilograms.
   The (CONVERT(C5,”ft”,”m”)+CONVERT(D5,”in”,”m”)) part converts feet and inches to meters and adds them up.
2. Press Enter to see the BMI in Cell F5.
3. Drag down the Fill Handle to get the BMI of the other individuals.

Method 3 – Calculate BMI in Cm and Kg

1. Select cell E5 and type the formula: =(D5/C5/C5)*10000
2. Press Enter to get the corresponding BMI in Cell E5.
3. Drag down the Fill Handle to BMI for the rest of the people.

You can also calculate the BMI percentile in Excel with the PERCENTRANK.INC function. The BMI percentile compares an individual’s BMI to others in a dataset. It denotes a relative value comparing the items in the whole dataset.
To calculate the BMI percentile, you can use the following formula: =PERCENTRANK.INC($E$5:$E$14,E5)

The PERCENTRANK.INC function returns a value’s rank in a dataset as a percentage. The E5:E14 array should be inside an absolute reference.

How to Use a BMI Calculator in Excel

In this section, we will show you how to use a BMI calculator in Excel. Below is a screenshot of the calculator.

Follow the steps below to use the BMI calculator efficiently:

1. Download the provided Excel file.
2. After that, right-click on the file and select Properties from the context menu.
3. Check the Unblock box > Apply > OK.
4. Click on the Enable Content security warning.
5. Select the desired unit from the drop-down in which you want the BMI.
6. Insert the desired inputs and press Enter.

You can follow the above-mentioned steps to use the BMI calculator easily.

How to Calculate BMI Z Score in Excel

The Z Score denotes a value’s position or rank in the dataset relative to the mean. Calculating BMI and Z Score will determine the relative position of that BMI in a dataset.

The formula for calculating Z-score is: (x-µ)/σ

• x = The raw data value
• µ = Mean/Average of the dataset
• σ = The standard deviation of the given dataset

We can follow some basic steps to calculate the BMI z-score in Excel. For example, we have a dataset of name, height, weight, and BMI in the range B5:E14. We will calculate the z-score in the range F5:F14.

Follow the steps below to calculate BMI Z-score in Excel:

1. Select Cell I4 and type the formula: =STDEV.P(E5:E14)
2. Press Enter to get the standard deviation of the calculated BMIs.
3. Select Cell I5 and type the following formula: =AVERAGE(E5:E14)
4. Hit Enter to get the average BMI of the dataset.
5. After that, type the formula in Cell F5 to get the Z-score for the first BMI: =(E5-$I$5)/$I$4
   The mean value and standard deviation have the absolute reference.

Download Practice Workbook

Conclusion

In this article, we have shown 3 cases of calculating BMI with formula in Excel. We have also added a BMI calculator that you can use to determine the BMI quickly. You can also calculate the z-score of BMI after reading the article. We get different formulas for calculating BMI because of different units of height and weight. You can get a clear concept about the formulas in this article. If you have any queries or suggestions, please let us know in the comment section.

<< Go Back to Formula List | Learn Excel