How to Use the BMI Formula in Excel – Step by Step Guide

In the world of health and fitness, it is important to track BMI. It is a key indicator to check if a person is healthy or not. In this article, you will learn how to calculate BMI in Excel.

Key Takeaways:

• BMI is a quick health indicator.
• BMI can check if a person’s weight is appropriate as per their height.
• It is used to categorize people as underweight, normal weight, overweight, and obese.
• It can be calculated using both metric and USC units.
• VBA automation can increase efficiency and speed.
• Use the correct formula and units for accurate results.

 

Understanding BMI

Body Mass Index (BMI) is a metric that is used to check if a person’s weight is appropriate as per their height. It is used to categorize people into ranges like underweight, normal weight, overweight, and obese. It can identify any risks because of excess body fat.

The BMI formula is a simple mathematical calculation. The formula is as follows:

• For the metric system, BMI = weight(kg) / height(m)^2
• For the USC units, BMI = (weight(lb) / height(in)^2) * 703)

 

Set Up Excel for BMI Calculation

Prepare Spreadsheet

To accurately calculate BMI in Excel, it’s essential to set up the spreadsheet correctly. Start by opening a new Excel document and labeling the necessary columns for easy reference. Typically, you will need columns for “Name,” “Height (m or in),” “Weight (kg or lb),” and “BMI.”

In the “Height” and “Weight” columns, you’ll input each individual’s data. Make sure the units are consistent with the formula you intend to use—kilograms and meters for the metric system, or pounds and inches for USC units. Next, create a column for the BMI calculation where you’ll enter the formula relevant to the unit system being used, ensuring that it’s error-free and logical.

Proper preparation of your spreadsheet is crucial for accurate and automated BMI calculations.

Common Mistakes to Avoid

• Using kilograms instead of pounds. This will skew your results.
• Using a formula that has an incorrect placement of parentheses.
• Using an incorrect cell reference. Make sure that the cells are locked before copying the formula
• Avoid data entry errors by checking the inputs.

 

How to Calculate BMI in Excel

Using the Metric System

You can easily calculate BMI using the metric system.

• Create columns for Height, Weight, and BMI.
• Enter each person’s height in meters.
• Enter the person’s weight in kg.
• In the BMI column, enter this formula:

= Weight / ([Height^2)

For example, if the weight is in cell B2 and the height is in cell C2, the formula should be:

= C2 / (B2^2)

This formula divides the weight by the square of the height. Once entered, you can drag the formula down the column to apply it to multiple entries. Remember to check your inputs for accuracy to ensure the correctness of the BMI values calculated. By following these steps, you can efficiently calculate BMI using the metric system in Excel.

Calculating with USC Units

For users preferring the US Customary (USC) units in Excel, calculating BMI requires slight modifications to the formula. Start by preparing your spreadsheet with columns labeled “Height (in),” “Weight (lb),” and “BMI.” Input the individuals’ height in inches and weight in pounds into the appropriate columns.

To calculate BMI using USC units, use the following formula:

= (Weight / (Height^2)) * 703

For instance, if the weight and height are mentioned in cells B2 and C2, the formula will be:

= (C2 / (B2^2)) * 703

Drag the formula down to apply it to the cells below.

BMI Classification

You can use the IF function to classify BMI results into different categories. The categories can be underweight, normal weight, overweight, and obese.

• Underweight – BMI is less than 18.5
• Normal Weight – BMI is between 18.5 and 24.9
• Overweight – BMI is between 25 and 29.9
• Obese – BMI is 30 and above.

In a new column, you can check the category as shown below:

 

Automate BMI Calculations

Follow the steps below to automate the calculation of BMI:

STEP 1: Press Alt + F11 to open the VBA editor.

STEP 2: Go to Insert > Module.

STEP 3: Enter the code.

STEP 4: Press Alt + F8 to run the macro. This will calculate BMI for all rows.

Using VBA automation will make sure that you get consistent and rapid results.

 

FAQs

What is BMI?

Body Mass Index (BMI) is a metric calculated based on someone’s height and weight. It tells you whether that person is healthy, malnutred or obese.

How to calculate the BMI formula?

You can use this formula to calculate BMI:

= Weight (kg) / (Height (m) ^ 2)

How to classify BMI?

You can use a nested IF formula to categorize people into underweight, normal, overweight, or obese.

=IF(BMI < 18.5,"Underweight", IF(BMI < 24.9,"Normal" ,IF(BMI < 29.9,"Overweight","Obese")))