This article shows you how to calculate the BMI percentile in Excel. A measure of body fat based on weight and height is called body mass index (BMI). Here, we will take you through 4 easy and convenient methods on how to calculate the BMI percentile in Excel.

**Table of Contents**hide

## Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

## What Is BMI Percentile?

The Body Mass Index, simply called BMI, is an easy and affordable approach to determine body fat using just height and weight. BMI is a screening technique that can be used to determine whether a person is underweight, overweight, or obese. The generic formula for calculating the BMI is as below.

`BMI = weight (kg) / { height (m) }`

^{2}

On the other hand, the BMI percentile represents relative value rather than an absolute figure. That means you can compare the individual’s BMI in relation to others using the BMI percentile. While calculating the BMI percentile, you need to compute the BMI first. Then you may determine the percentile in percentage.

## 4 Methods to Calculate BMI Percentile in Excel

Suppose, we have a **Height and Weight List** of some individuals including their **Names**, **Heights** in **Feet** and **Inches**, and **Weights** in **lbs**.

We want to calculate their BMI as well as their BMI percentile in Excel. So, without further ado, let’s jump into the different methods.

### 1. Using CONVERT and PERCENTRANK.INC Functions

Our first method comprises two different functions. To know more, follow the steps below.

**Steps:**

- At First, select cell
**F6**. Write down the formula below and press**ENTER**.

`=CONVERT(C6,"ft","m")+CONVERT(D6,"in","m")`

Here, **C6** and **D6** represent the **Height** in **Feet** and **Inches** respectively.

**Formula Breakdown:**

We used the **CONVERT function** to convert the height in feet to meters. Again, we converted the height in inches into meters using the **CONVERT **function and added it to the previous portion of the formula with a **( + )** sign. Thus, we got the total height just in the meter unit.

- Secondly, use the
**Fill Handle**tool and drag it down till cell**F15**. Therefore, we get the results of other cells.

- After that, we have to convert the weight from lbs to kg unit. Therefore, select cell
**G6**and write down the formula below. Then, press**ENTER**key.

`=CONVERT(E6,"lbm","g")/1000`

In this place, **E6** serves as *Weights* in *lbs*.

*Note:** There is no default system in Excel to convert pounds into kilograms directly. So, first, we convert it into grams, then convert it into kilograms by dividing it by 1000.*

- For calculating BMI, first, select cell
**H6**and type the formula below. Now, press**ENTER**.

`=G6/F6^2`

- Later, select cell
**I6**. Type in the formula below and press**ENTER**.

`=PERCENTRANK.INC($H$6:$H$15,H6)`

Here, **H6:H15** means the range of these 10 people’s BMI, and just **H6** represents the **BMI** of **Robin**.

The rank of a value in a data set is returned by the **PERCENTRANK.INC function** as a percentage of the entire data set. This function assesses a value’s position within a data set.

We can see that **Mary** has the lowest BMI at **17.71**. On the other hand, **Thomas** has the highest BMI at **33.96**. These 2 entities are represented as **0%** and **100%** respectively. All the other BMIs are in this range. For example, Robin has a *BMI Percentile* of **22**. This means his BMI is greater than that of 22% of other people in this list.

**Read More:** **How to Use the CONVERT Function in Excel and creating a BMI Calculator template**

### 2. Applying CONVERT, POWER, and PERCENTRANK.INC Functions

In our second method, we’ll use the **POWER function** instead of using an index manually. Follow our steps carefully.

**Steps:**

- At First, calculate the values up to column
**G**just like we did in**Method 1**. This means finding out the*Height (m*) and*Weight (kg)*like in our previous method. - Secondly, select cell
**H6**. Type in the formula below and press**ENTER**.

`=G6/POWER(F6,2)`

Here, **F6** and **G6** represent the *Height (m)* and *Weight (kg)*.

- Later, select cell
**I6**and write down the same formula as in**Method 1**to get the BMI percentile.

**Read More:**** How to Calculate BMI in Excel (2 Handy Ways)**

### 3. Implementing Arithmetic Formula to Calculate BMI Percentile in Excel

In this method, we are using an arithmetic formula to calculate BMI from weight(lbs) and height(inches). The generic formula is as below.

`BMI = weight (lbs) / { height (inches) }`

^{2}` * 703`

The main advantage of using this formula is that you don’t have to convert your height and weight to meters and kilograms. Follow our steps below to understand the method completely.

**Steps:**

- At first, select cell
**F6**. Write down the formula below and press**ENTER**.

`=C6*12+D6`

However, we converted the height of cell **C6** from feet into inches by multiplying it with 12. Then add it to cell **D6** to get the whole height in inches.

- Secondly, select cell
**G6**and put the formula as shown below. Then, press**ENTER**

`=E6/F6^2*703`

Here, **E6** and **F6** serve as *Weight(lbs)* and *Height(inch)*.

- Finally, calculate the BMI percentile just like we did in
**Method 1**.

**Read More:** **How to Use Formula for Tracking Weight Loss in Excel (2 Examples)**

### 4. Employing VBA Code to Calculate BMI Percentile in Excel

Implementing the **VBA** code is an alternative way to solve this problem. Applying the **VBA** code is exciting all the time. Here, we’ve already calculated the *Height (inch)* like **Method 3**. Using the formula from our previous method, we will build a user-defined function through **VBA** coding to calculate BMI.

Notice the steps carefully.

**Steps:**

- At First, right-click on the
**Sheet Name**and select**View Code**.

- However, a window named
**Microsoft Visual Basic for Applications**opens. After that, select**Insert**>**Module**.

- At this point, a coding module opens. Write down the code as shown below in that module.

```
Function BMI(w, h)
'Here, w means weight, h means height
BMI = (w / (h) ^ 2) * 703
End Function
```

- Later, click on the
**Save**icon and save the workbook on your PC.

- After that, return to the
**VBA**worksheet. Then, select cell**G6**and start typing**bmi**with a preceding**( = )**sign. You can see that Excel is already showing the**BMI**function as a suggestion.

- After that, select the function by pressing
**TAB**on the keyboard. Then, Give the argument as cell references**E6**and**F6**.

- Lastly, find the
*BMI Percentile*like**Method 1**in cell**H6**.

**Read More:** **How to Create a Body Mass Index (BMI) Calculator in Excel Using VBA **

## Conclusion

Thank you for reading this article. We hope you find it helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, **Exceldemy**, to explore more.