## 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))))))`

- Press the
**Enter**button and drag the**Fill Handle**icon down.

**How to Calculate the BMI Percentile in cm and kg**

**Steps:**

- We will convert the weight from lbm to kg with the following formula in
**Cell F5**.

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

- Press
**Enter**and drag down the**Fill Handle**icon.

- Apply the following formula in
**Cell G5**to get the height in cm from feet and inches.

`=CONVERT(D5,"ft","cm")+CONVERT(E5,"in","cm")`

- Pull the
**Fill Handle**icon down.

- Move to
**Cell H5**. - Insert a formula to calculate the
**BMI**from**cm**and**kg**:

`=ROUND((F5/POWER(G5/100,2)),0)`

- Drag the
**Fill Handle**icon down.

- Put the following formula in
**Cell I5**.

`=PERCENTRANK.INC($H$5:$H$11,H5)`

- Pull the
**Fill Handle**icon down.

We get the percentile value for the **BMI** based on the highest value in the table.

**Download the Practice Workbook**

**<< Go Back to BMI | Formula List | Learn Excel**