21.8 Basal Metabolic Rate

In this final example, you are asked to combine what you have learned to create a custom function to calculate Basal Metabolic Rate (BMR). While the requirements might initially look intimidating, they are simpler than they appear.

Your BMR is the number of calories your body burns in a day. There are several ways of calculating your BMR; however, the Mifflin-St Jeor equations give the most reliable results and are calculated as follows:

Male: BMR = 10 * (weight in kg) + 6.25 * (height in cm) – 5 * age + 5
Female: BMR = 10 * (weight in kg) + 6.25 * (height in cm) - 5 * age - 161

Note: 1 kilogram (kg) = 2.20462262 pounds (lbs)
          1 inch = 2.54 Centimeters (cm)

Create a function called “BMR” that takes four inputs (see below) and returns the BMI.

Notice that the inputs for our data will be in pounds and inches, but the calculation is in kilograms and centimeters. You can use the conversion rates above; alternatively, you could choose to use the CONVERT worksheet function.

Go ahead and implement your own function without looking at our solution below.

Here is one potential solution. Notice that it really isn’t too complex. We select which calculation we will do based on the sex that is passed and then simply use the parameters in the appropriate spots in each calculation.

Show Code

Suppose you wanted to get an even more accurate rating of BMR by using an activity multiplier. To get your true BMR, you need to multiply the above equation with your activity multiplier.

Sedentary = BMR * 1.2 (little or no exercise, desk job)
Lightly-active = BMR * 1.375 (light exercise/sports 1–3 days/wk)
Mod-active = BMR * 1.55 (moderate exercise/sports 3–5 days/wk)
Very-active = BMR * 1.725 (hard exercise/sports 6–7 days/wk)
Extr-active = BMR * 1.9 (hard daily exercise/sports and physical job, or 2x/day training, like marathon or contest training)

Create a new function called “bmrActual” that takes five inputs (see below) and returns the adjusted BMI (i.e., bmrActual(M,210,74,25,"Sedentary").

Rather than redoing the calculation you did above, this function should call the BMR function you already wrote (passing four values onto it) and then multiply the returned BMR times the appropriate activity multiplier.

Here is our implementation of the bmrActual function. Notice how we simply call the existing BMR function, passing the parameters that were passed to the bmrActual function onto the existing BMR function. We then store the result in bmrActual and use a Case statement for the activity level.

Show Code

Writing functions in Excel is a very powerful skill. If you were able to write the functions in this lesson, you are well on your way to creating tremendous value in your internship or job. Look for ways you can take complex calculations, create UDFs, and then allow less sophisticated Excel users to use those functions.