How to Use the CONVERT Function in Excel and creating a BMI Calculator template
The CONVERT function is an extremely useful and relatively unknown Excel function. It converts a number from one unit of measurement to another unit of measurement. The CONVERT function can convert between units of weight and mass, units of distance, units of time, pressure, force, temperature and energy among others. For example, one can input temperature degrees in Celsius and the CONVERT function will then deliver the units in degrees Fahrenheit, as specified.
The syntax of the CONVERT function is:
So, let’s get started with an example to illustrate the use of the CONVERT Function.
There are two dominant systems of measurement globally, the one is the US customary unit system and the other is the metric system. The US customary unit system is derived from the British system which was in use, prior to the US declaring its independence. The metric system gained popularity in the rest of the world, due to its being championed by British scientists and philosophers during the latter part of the 1800s.
In our example, a hypothetical world-renowned dietician is trying to work out whether the diet plans she recommended for her US-based clients and international clients were effective. She is using the Body Mass Index (BMI) calculation to provide an indication, for the success of the plans for each client.
The BMI calculation is obtained, by taking the measurement of the person at hand’s weight in kilograms (kg) and then dividing by the square of the person’s height measured in meters (m).
BMI = weight (kg)/height2(m2)
In terms of the US system of measurement, the BMI is obtained by dividing the person’s weight in pounds (lb) by the square of their height in inches (in) and then multiplying by the conversion factor.
BMI = weight (lb)/height2(in2) x 703.0704
A BMI of less than 18.5 indicates that the person is underweight. Between 18.5 and 24.9 is considered to be normal. BMI’s between the range of 25.0 and 29.9 are considered to be overweight. A BMI of greater than 30.0 in general, indicates obesity.
The dietician wants to input the weight in kilograms (kg) and have it be automatically converted to pounds (lbs). She also wants to input the height of her client in meters (m) and have it be automatically converted to inches (in). The calculation for BMI is needed in both systems and she wants an indication of whether the BMI indicates an underweight, normal, overweight or obese client. Thus she will conclude, based on the BMI as an indicator, whether the diet plan she recommended was effective or not.
The source data is shown below:
1) First things first, we want to convert the weight given in kilograms in Cell B5, to pounds in Cell B6.
2) So, in Cell B6, we input the following formula:
Where number is the actual input number in Cell B5, we type the text “kg” as the from_unit and make sure that this is enclosed in quotation marks, the to_unit is entered as “lbm” since in this case, we want to convert from kilograms to pounds.
3) Press CTRL-ENTER and a value of 275.578 lbs is delivered to the cell as shown.
4) Now we want to calculate height in inches, based on the given height value in meters.
5) So in Cell B8, we input the following formula:
Where in this case, the number is the actual input number in Cell B7, the from_unit is entered in “m” and make sure again that this is enclosed in quotation marks, the to_unit is entered as “in” since in this case we want to convert from meters to inches.
6) Press CTRL-ENTER and a value of 70.8661 inches is delivered to the cell as shown.
7) In Cell B9 we enter the following formula, in order to calculate BMI using the metric measurements.
8) Upon pressing CTRL-ENTER, a BMI of 38.6 is delivered.
9) We now want to calculate BMI using pounds and inches. So in Cell B10, we input the following formula:
Where B6 denotes the weight in pounds and B8 denotes the height in inches and 703.0704 is the conversion factor.
10) Upon pressing CTRL-ENTER, we get the same value of 38.6, as expected.
11) In Cell B11, we type the following IF formula based on if the BMI is less than 18.5, the formula must deliver underweight, diet plan needs to be updated, if the BMI falls between 18.5 and 24.9, the formula must deliver normal weight, diet plan fine. If the BMI is between 25.0 and 29.9, the formula must deliver slightly overweight, diet plan needs to be updated. Finally, if the BMI is 30.0 or greater then the formula needs to deliver obese, diet plan needs to be updated.
=IF(B9<18.5,”underweight, diet plan needs to be updated”,IF(B9<=24.9,”normal weight, diet plan fine”,IF(B9<=29.9,”slightly overweight, diet plan needs to be updated”,”obese, diet plan needs to be updated”)))
12) Upon pressing CTRL-ENTER, we get the result: obese, diet plan needs to be updated.
The dietician decides she would like to create a template based on the process, she followed above in order to reuse the BMI calculation, every time she needs to calculate BMI.
1) The first thing we have to do is open a blank workbook.
2) We then format the workbook accordingly as shown below:
3) We now want to create a few defined names, to use in our formulas.
4) Go to Formulas>Defined Names>Define Name and click on the drop down arrow and select Define Name as shown below.
5) In the New Name Dialog Box, we enter weight_kg as the Name, leave Scope as workbook and select Cell B7 in the refers to the section as shown below.
6) Click Ok.
7) In Cell B8, enter the formula using the named range:
8) Press CTRL-ENTER.
9) Follow the same process above to name Cell B9, height_m.
10) In Cell B10 type the following formula:
11) Press CTRL-ENTER.
12) In Cell B11, enter the formula:
The reason why we are encapsulating our formula in an IFERROR is that when we open the template, we ideally want blank entries or zeroes (we don’t want to be filled in data), so the formulas will evaluate to the #DIV/0! error (since in the formulas one is dividing by zero) until the user actually enters values for the weight and height. In order to avoid the #DIV/0! appearing and users getting confused and perhaps thinking the actual template is not working, we use the IFERROR function.
13) Press CTRL-ENTER.
In Cell B12, type the following formula:
14) In Cell B13, we type the following formula:
=IF(B11<18.5,”underweight, diet plan needs to be updated”,IF(B11<=24.9,”normal weight, diet plan fine”,IF(B11<=29.9,”slightly overweight, diet plan needs to be updated”,IF(B11=” “,” “,”obese, diet plan needs to be updated”))))
Our extra section in the last part of the formula is to accommodate the error value that will be generated, captured by the IFERROR and a space inserted until the user fills in the weight and height.
15) Press CTRL-ENTER.
16) Go to Page Layout>Page Setup>Orientation and change the Orientation of the page from Portrait to Landscape as shown.
17) Go to Page Layout> Expand the Scale to Fit Option.
18) In the Page Setup Dialog Box, select the Margins tab, and make sure under Center on Page, Horizontally is checked, and then click Ok.
19) In order to save the workbook as an Excel template, Click File>Save as. In the Save As type choose Excel Template, and give your template a descriptive name, in this case, we will go with BMICalculator as shown below.
20) Once you change the file type to template, Excel will automatically change the default folder to the default folder where templates are stored on your PC.
21) Close the Workbook.
22) In order to use the template, you just created, open a blank workbook, then go to File>New>Personal and the template you created should appear below. Click on the BMICalculator.
23) Initially, the template is blank until you insert the weight in kg in Cell B7 and the height in meters in Cell B9. Once you enter these values, the pounds, inches, BMI calculations and diet plan recommendations will automatically be calculated.
24) Fill in the Client Name, Location and the weight in kg and height in m, and everything else will be calculated automatically as shown below.
25) And there you have it.
Download Working File
The CONVERT function provides a way to convert between the metric and US customary units with ease. If you frequently need to convert between the two systems, you can create a template for ease of reuse.
Please feel free to comment and tell us what you use the CONVERT Function for and which Excel templates you like using.