Physical examination

Are you overweight? Weight gain is not just an aesthetic problem, it can be the cause of many diseases, so companies conduct an annual physical examination to check the health of their employees. In this example, you will learn how to calculate BMI (Body Mass Index), a coefficient that indicates whether you are below or above the optimal body weight, and how to use conditional formatting to mark those who have unfavorable BMI.

We will start this example with the table of employees, which contains the name and surname, gender, age, height, weight and some other data. The Body Mass Index (BMI) is calculated according to the weight / height² formula. If we express the height in meters, write the formula, and then round the result to two decimal places, it should look like this:

=ROUND(H3/POWER(G3/100,2),2)

By simply copying the formula, we will calculate the BMI for each of the employees.

How do we determine which employees have an unfavorable BMI? We will use the conditional formatting technique! In the Home ribbon, choose Conditional Formatting/New Rule, and then select that you want to enter a formula to create a conditional formatting rule. Enter the formula:

=NOT(AND(($I3>=18,5),($I3<25)))

and then select a formatting that colors the cell background to yellow. The result of this action is that all those whose BMI is not in the range of 18.5 and 25 will be marked within the table.