Your weight is an important indicator of your health and well-being. It can affect your risk of developing certain diseases, such as diabetes, heart disease, and some cancers. However, there is no single ideal weight that applies to everyone, as different factors such as height, age, gender, and body composition can influence it. Therefore, it is useful to know how to calculate your ideal and recommended weights based on various methods and criteria.
In this article, we will show you how to use Excel formulas to calculate your ideal and recommended weights based on four common methods: body mass index (BMI), Devine formula, Robinson formula, and Miller formula. We will also explain the basic theory behind each method, the procedures to follow, and the advantages and limitations of each method. Finally, we will provide a scenario to give a detailed example with real data and show you how to use an Excel table to display the results.
The Basic
Body Mass Index (BMI)
Body mass index (BMI) is a measure of body fat based on your weight and height. It is calculated by dividing your weight in kilograms by your height in meters squared. BMI can be used to estimate your health risk and determine if you are underweight, normal weight, overweight, or obese. The following table shows the BMI categories and their corresponding health risks1:
| BMI Category | BMI Range (kg/m2) | Health Risk |
|---|---|---|
| Underweight | Less than 18.5 | Increased |
| Normal weight | 18.5 to 24.9 | Average |
| Overweight | 25 to 29.9 | Increased |
| Obese | 30 or more | High |
The advantages of using BMI are that it is simple, easy, and widely used. It can also be applied to both adults and children. However, the limitations of using BMI are that it does not account for muscle mass, bone density, body shape, or ethnic differences. Therefore, it may not accurately reflect your body fat percentage or health status.
Devine Formula
The Devine formula is one of the oldest and most popular formulas for estimating ideal body weight (IBW). It was developed by Dr. BJ Devine in 1974 for use in medical settings, especially for calculating drug dosages. The formula is based on the assumption that the ideal weight for a person of a given height is proportional to their height. The formula is different for men and women, as shown below:
- For men: IBW (in kilograms) = 50 + 2.3 * (height in inches over 5 feet)
- For women: IBW (in kilograms) = 45.5 + 2.3 * (height in inches over 5 feet)
The advantages of using the Devine formula are that it is simple, easy, and widely used. It can also be adjusted for different body frames by adding or subtracting 10% of the result. However, the limitations of using the Devine formula are that it is based on an arbitrary assumption and does not account for body composition, age, or ethnicity. Therefore, it may not accurately reflect your ideal weight or health status.
Robinson Formula
The Robinson formula is a modification of the Devine formula that was proposed by Dr. JD Robinson in 1983. It is based on the same assumption as the Devine formula, but uses different coefficients for men and women, as shown below:
- For men: IBW (in kilograms) = 52 + 1.9 * (height in inches over 5 feet)
- For women: IBW (in kilograms) = 49 + 1.7 * (height in inches over 5 feet)
The advantages of using the Robinson formula are that it is simple, easy, and widely used. It can also be adjusted for different body frames by adding or subtracting 10% of the result. However, the limitations of using the Robinson formula are that it is based on an arbitrary assumption and does not account for body composition, age, or ethnicity. Therefore, it may not accurately reflect your ideal weight or health status.
Miller Formula
The Miller formula is another modification of the Devine formula that was proposed by Dr. DG Miller in 1983. It is based on the same assumption as the Devine formula, but uses different coefficients for men and women, as shown below:
- For men: IBW (in kilograms) = 56.2 + 1.41 * (height in inches over 5 feet)
- For women: IBW (in kilograms) = 53.1 + 1.36 * (height in inches over 5 feet)
The advantages of using the Miller formula are that it is simple, easy, and widely used. It can also be adjusted for different body frames by adding or subtracting 10% of the result. However, the limitations of using the Miller formula are that it is based on an arbitrary assumption and does not account for body composition, age, or ethnicity. Therefore, it may not accurately reflect your ideal weight or health status.
Procedures
To calculate your ideal and recommended weights in Excel formula, you need to follow these steps:
- Enter your weight and height in cells A2 and B2, respectively. You can use any unit of measurement, such as kilograms, pounds, meters, or inches, as long as you are consistent throughout the calculations.
- Convert your weight and height to kilograms and meters, respectively, if they are not already in those units. You can use the following conversion factors:
- 1 kilogram = 2.20462 pounds
- 1 meter = 39.3701 inches
- To convert from pounds to kilograms, divide by 2.20462
- To convert from inches to meters, divide by 39.3701
- Enter the following formulas in cells C2 to F2 to calculate your BMI, Devine, Robinson, and Miller weights, respectively:
- BMI:
=A2/B2^2 - Devine:
=IF(B2<=60,50,50+2.3*(B2-60))for men, or=IF(B2<=60,45.5,45.5+2.3*(B2-60))for women - Robinson:
=IF(B2<=60,52,52+1.9*(B2-60))for men, or=IF(B2<=60,49,49+1.7*(B2-60))for women - Miller:
=IF(B2<=60,56.2,56.2+1.41*(B2-60))for men, or=IF(B2<=60,53.1,53.1+1.36*(B2-60))for women
- BMI:
- Format the cells C2 to F2 as numbers with one decimal place.
- Compare your actual weight with your ideal and recommended weights and see which category you fall into based on the BMI table.
Example
To illustrate the procedures, let’s use a scenario with real data. Suppose you are a 35-year-old man who weighs 180 pounds and is 6 feet tall. You want to know your ideal and recommended weights in Excel formula. Here is how you can do it:
- Enter your weight and height in cells A2 and B2, respectively. In this case, A2 = 180 and B2 = 72.
- Convert your weight and height to kilograms and meters, respectively, if they are not already in those units. In this case, you need to divide A2 by 2.20462 and B2 by 39.3701. You can enter the following formulas in cells A3 and B3, respectively, and then copy the values to cells A2 and B2:
- A3:
=A2/2.20462 - B3:
=B2/39.3701 - A2: 81.6
- B2: 1.8
- A3:
- Enter the following formulas in cells C2 to F2 to calculate your BMI, Devine, Robinson, and Miller weights, respectively:
- BMI:
=A2/B2^2 - Devine:
=IF(B2<=60,50,50+2.3*(B2-60)) - Robinson:
=IF(B2<=60,52,52+1.9*(B2-60)) - Miller:
=IF(B2<=60,56.2,56.2+1.41*(B2-60)) - C2: 25.2
- D2: 77.4
- E2: 74.2
- F2: 73.0
- BMI:
- Format the cells C2 to F2 as numbers with one decimal place.
- Compare your actual weight with your ideal and recommended weights and see which category you fall into based on the BMI table. In this case, your actual weight is 81.6 kg, which is higher than your ideal and recommended weights of 77.4 kg, 74.2 kg, and 73.0 kg, respectively. Your BMI is 25.2 kg/m2, which puts you in the overweight category and indicates an increased health risk. Therefore, you may want to consider losing some weight to reach your ideal and recommended weights and lower your BMI.
Excel Table
To display the results of your calculations in a more organized and visual way, you can use an Excel table. Here is how you can do it:
- Select the range A1:F2 and click on the Insert tab on the ribbon.
- Click on the Table button and check the box that says “My table has headers”.
- Click OK and you will see a table with filters and formatting applied to your data.
- You can resize, sort, filter, or customize your table as you wish.
Here is an example of how your table might look like:
| Weight (kg) | Height (m) | BMI (kg/m2) | Devine (kg) | Robinson (kg) | Miller (kg) |
|---|---|---|---|---|---|
| 81.6 | 1.8 | 25.2 | 77.4 | 74.2 | 73.0 |
Other Approaches
Besides the four methods we discussed, there are other approaches to calculate your ideal and recommended weights, such as:
- Waist-to-height ratio (WHtR): This is a measure of your waist circumference divided by your height. It can be used to assess your abdominal fat and health risk. A WHtR of 0.5 or less is considered healthy.
- Waist-to-hip ratio (WHR): This is a measure of your waist circumference divided by your hip circumference. It can be used to assess your body shape and health risk. A WHR of 0.9 or less for men and 0.85 or less for women is considered healthy.
- Body fat percentage (BFP): This is a measure of your total body fat divided by your total body weight. It can be used to assess your body composition and health status. A BFP of 10-20% for men and 18-28% for women is considered healthy.
You can use online calculators, scales, or other devices to estimate these measures. However, they may not be very accurate or reliable, as they depend on various factors and assumptions. Therefore, you should always consult your doctor or a qualified professional before making any decisions based on these measures.
