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.
CONVERT(number, from_unit, to_unit)
|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.
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.
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.
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.
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:
Generic Formula to Convert Inches to Meters:
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.
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.
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
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.
- 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.
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.
- If you don’t like to add additional columns to calculate BMI, directly apply the following formula instead.
- 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))))))
- 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.
- 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.
- 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.
- 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.
- 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.
- Pull the Fill Handle icon downwards.
- Move to Cell H5. Insert a formula to calculate the BMI from cm and kg.
- Drag the Fill Handle icon again.
- We want to get the percentile here. Finally, put the formula on Cell I5.
- For the last time, pull the Fill Handle icon.
Finally, we get the percentile value from BMI.
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.