How to Use the CONVERT Function to Calculate BMI in Excel

BMI is a common word for health-conscious people. Now, people are becoming more careful about health. BMI is one of the commonly known indicators for the mass of people to get information about their physical condition. This article will have a detailed discussion on how to calculate BMI in Excel using the CONVERT function.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to Excel CONVERT Function

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

Syntax:

CONVERT(number, from_unit, to_unit)

Arguments:

Argument Required/Optional Explanation
number Required The value which 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. There is BMI standard chart indicating the health condition showing the height and weight.

BMI Chart

 


Formulas of 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.

We know two measuring systems exist according to different regions of the world. The Imperial system exists in most of the British-ruled countries. The metric system is the modern unit system. Most countries are now replacing the imperial system with this metric unit system.

Basic BMI Formula

Formula to Calculate BMI in Imperial Unit:

In the imperial unit system, the weight represents by the pound (lbm) and height in terms of feet or inches.

BMI formula with imperial units

This is the formula of BMI in the imperial system unit.

Formula to Calculate BMI in Metric Unit:

In the metric unit system, the weight represents by the kilogram (kg) and height in meters (m). This is the simplest formula for BMI.

BMI formula with metric units

This is the formula of BMI in metric system units.


How to Use Excel CONVERT Function to Calculate BMI

Now, you see the simplest formula to calculate BMI is the following,

Hence, if you have weight and height data in any other units, it is better to convert them into the metric system. The CONVERT function will help you in this way.

For example, if you have weight in Pounds, and height in Inches, you better convert them to Kilograms and Meters first. To do that, you have to apply the following formulas.

Generic Formula to Convert Pounds to Kilograms:

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

Generic Formula to Convert Inches to Meters:

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

Here we see some more weight and length units in short form, usable for the CONVERT function. Just pick the suitable short form for you from the list the function is providing.

CONVERT function units short form list


Steps to Calculate BMI Utilizing Excel CONVERT Function

In our sample dataset, height and weight are given in the imperial unit. But here, we want to calculate the BMI in the metric unit. So, we need to convert the weight and height into a metric unit. And we will do this using the CONVERT function. Let’s have a look at the full process.

Sample dataset for BMI calculation in Excel

This is our sample dataset. It contains the weight and height of some people. We will calculate the BMI of those people using this dataset. On the right side, there is a chart. This chart indicated the different BMI value levels and based on those levels indicated the physical condition of individuals.


📌 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).
  • Now, move to Cell E5.
  • Put the formula based on the CONVERT
  =CONVERT(C5,"lbm","kg")

Using CONVERT function in Excel

We want to convert weight from pound to kg. For that choose lbm to kg.

  • Pull the Fill Handle icon after pressing Enter.

  • Again, we apply a formula based on the CONVERT function on Cell F5 for height.
  =CONVERT(D5,"in","m")

Use CONVERT function to convert height from inches to meter in Excel

  • Drag the Fill Handle icon downwards.

Here, we converted the height from inches to meters.


📌 Step 2: Calculate BMI Using Formula

  • Now, we want to calculate BMI at Cell G5.
  =ROUND(E5/POWER(F5,2),0)

Calculate BMI with the combination of the ROUND & POWER functions with values derived from Excel CONVERT function

One thing is noticeable, there is no multiplying factor as used in the imperial unit.

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

Calculated BMI with values derived from Excel CONVERT function

Note:

  • If you don’t like to add additional columns to calculate BMI, directly 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: Interpret Health Condition from Predefined BMI Chart Using Another Formula

  • Now, we want to get comments based on the BMI value of Cell H5.
  • Put the following formula here.
=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)))))) 

Interpret Health Condition from Predefined BMI Chart Using Another Excel Formula

  • Darg the Fill Handle icon.

Finally, we calculate the BMI using the weight and height in the metric unit after converting them.


Calculate BMI with Weight and Height in Imperial Unit

Here, we will calculate the BMI with weight and height in the Imperial unit. In the imperial unit system, the unit of weight in pounds (lbs) and height is in feet, and inches. We will use the combination of the round and power function in this calculation.

📌 Steps:

  • First, we add two columns on the right for BMI and comment.

  • Move to Cell E5.
  • Put the following formula based on the combination of the ROUND and POWER functions.
=ROUND(C5/POWER(D5,2)*703,0)

Calculate BMI in metric imperial unit in Excel

  • Then, press the Enter button to get the result.

  • Drag the Fill Handle icon downwards

We get the BMI value for the full dataset.

  • This time move to Cell F5 and put the formula below.
=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))))))

  • Again, press the Enter button and Pull the Fill Handle icon.

Finally, we get BMI with comments for the full dataset.


How to Calculate BMI Percentile in Cm and Kg Units

The PERCENTRANK.INC function returns the rank of a value in a dataset as a percentage of the data set as a percentage (0..1 inclusive) of the data set.

In this section, we want to find out the BMI percentile. First, we will find out the BMI from cm and kg. Then use this BMI value to calculate the percentile.

📌 Steps:

  • Here, we can see that height is given in feet and inches. We will convert this into cm. Apply the following formula on Cell F5.
  =CONVERT(C5,"lbm","kg") 

applying CONVERT function

  • Press Enter and drag the Fill Handle icon.

  • Again, apply another formula on Cell G5 based on the CONVERT function to get height in cm from feet and inches.
=CONVERT(D5,"ft","cm")+CONVERT(E5,"in","cm")  

Convert height from feet and inches to cm in Excel using CONVERT function in Excel

  • Pull the Fill Handle icon downwards.

  • Move to Cell H5. Insert a formula to calculate the BMI from cm and kg.
  =ROUND((F5/POWER(G5/100,2)),0)  

Calculate BMI for Percentile in Excel

  • Drag the Fill Handle icon again.

Using Excel fill handle tool

  • We want to get the percentile here. Finally, put the formula on Cell I5.
   =PERCENTRANK.INC($H$5:$H$11,H5)

Calculate BMI Percentile in Cm and Kg Units in Excel

  • For the last time, pull the Fill Handle icon.

Finally, we get the percentile value from BMI.


Conclusion

In this article, we described how to calculate BMI in the metric and imperial units. We used the CONVERT function to calculate the BMI in Excel. I hope this will satisfy your needs. Please look at our website Exceldemy.com and give your suggestions in the comment box.

Taryn N

Taryn N

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo