How to Use the CONVERT Function to Calculate BMI in Excel

Introduction to the Excel CONVERT Function

The CONVERT function converts a number from one measurement system to another. It was introduced in Excel 2003 and is available for all versions after that.

Syntax:

CONVERT(number, from_unit, to_unit)

Arguments:

Argument	Required/Optional	Explanation
number	Required	The value that you want to convert from one unit to another
from_unit	Required	The unit from which you want to convert
to_unit	Required	The unit to which you want to convert

What Is BMI?

BMI stands for Body Mass Index. It is the measurement of body fat depending on the weight and height of the individual. Here is the BMI standard chart indicating the health condition showing the height and weight.

Formulas for BMI in Metric and Imperial Units

There is a simple mathematical formula to calculate BMI. This formula is based on the height and weight of an individual.

Formula to Calculate BMI in Imperial Unit:

In the imperial unit system, the weight is in pounds (lbm) and the height in inches. To ensure it aligns with the metric formula, it uses a constant multiplier.

Formula to Calculate BMI in Metric Unit:

In the metric unit system, the weight is in kilogram (kg) and the height is in meters (m).

How to Use the CONVERT Function to Calculate BMI in Excel

If you have weight and height data in non-metric units, it is better to convert them into the metric system. The CONVERT function will help you in this way.

Generic Formula to Convert Pounds to Kilograms:

=CONVERT(value,"lbm","kg")

Generic Formula to Convert Inches to Meters:

=CONVERT(value,"in","m")

When entering the function manually, you will get a list of available units. Pick the suitable short form from the list.

Steps to Calculate BMI with the Excel CONVERT Function

In our sample dataset, height and weight are given in the imperial unit. We’ll convert the units, then calculate the BMI. We’ll use the reference chart on the right to provide a comment for each person.

Step 1 – Add Two More Columns to Convert Heights and Weights into Metric Units

We added two extra columns for the dataset for Weight (kg) and Height (m).
Move to Cell E5.
Put the following formula:

=CONVERT(C5,"lbm","kg")

Pull the Fill Handle icon after pressing Enter.

Apply the following formula in Cell F5 for the height conversion.

=CONVERT(D5,"in","m")

Drag the Fill Handle icon down.

Step 2 – Calculate the BMI Using Formula

Calculate the BMI in Cell G5.

=ROUND(E5/POWER(F5,2),0)

Pull the Fill Handle icon down to get the result for the whole column.

Note:

If you don’t want to add additional columns to calculate BMI, apply the following formula instead.

=ROUND(CONVERT(C5,"lbm","kg")/POWER(CONVERT(D5,"in","m"),2),0)

You can also ignore the ROUND function. It is just rounding the BMI results here. You can calculate BMI without using it.

Step 3 – Create a Comment Based on the Reference Table

We want to get comments based on the BMI value.

Put the following formula in H5.

=IF(AND(G5>='Data Set'!$F$6,G5<='Data Set'!$G$6),'Data Set'!$H$6,IF(AND(G5>='Data Set'!$F$7,G5<='Data Set'!$G$7),'Data Set'!$H$7,(IF(AND(G5>='Data Set'!$F$8,G5<='Data Set'!$G$8),'Data Set'!$H$8,(IF(G5>='Data Set'!$F$9,'Data Set'!$H$9))))))

Drag the Fill Handle icon down.

Calculate BMI with Weight and Height in Imperial Units

Steps:

Add two columns on the right for the BMI and comment.

Insert the following function in Cell E5:

=ROUND(C5/POWER(D5,2)*703,0)

Press the Enter button to get the result.

Drag the Fill Handle icon down.

We get the BMI value for the full dataset.

In Cell F5, insert the following:

=IF(AND(E5>='Data Set1'!$F$6,E5<='Data Set1'!$G$6),'Data Set1'!$H$6,IF(AND(E5>='Data Set1'!$F$7,E5<='Data Set1'!$G$7),'Data Set1'!$H$7,(IF(AND(E5>='Data Set1'!$F$8,E5<='Data Set1'!$G$8),'Data Set1'!$H$8,(IF(E5>='Data Set1'!$F$9,'Data Set1'!$H$9))))))