Explanation
This formula uses the “greater than or equal to” operator with text, something you might not have tried before. When Excel compares text, it decides which value is “greater” than another based on rules that follow the ASCII specification .
Inside COUNTIF, the range argument is supplied as the named range “countries” (B4:B13), and the criteria is supplied as “less than or equal to” the value in C5. In each row, COUNTIFS returns the number of values that are less than or equal to the current value, which creates a sequential list of numbers (i.e. a rank) in the helper column .
Listing sorted values
The helper column can be used to retrieve sorted values by rank. In E5, the formula used to retrieve values is:
=INDEX(countries,MATCH(ROWS($E$5:E5),helper,0))
This is an INDEX and MATCH formula that uses an expanding reference to generate sequential numbers, which are fed into MATCH as lookup values. MATCH figures out where each number exists in the data, and INDEX retrieves the value at that position. See this page for a more detailed explanation .
Handling duplicates
If the data contains duplicate text values, the sequence of sort numbers will also contain duplicates, which will cause problems if you are trying to retrieve values with the INDEX function. To work around this problem, you can use a variation of the formula that increments duplicates with a second COUNTIF:
=COUNTIF(countries,"<"&B5)+COUNTIF($B$5:B5,B5)
Note the logical operator in the first COUNTIF function has been adjusted, and the range in the second COUNTIF function is an expanding reference .
Explanation
This example shows one way to calculate BMI (Body Mass Index) in Excel. The standard BMI formula is:
BMI = weight (kg) / height (m) 2
The approach used here is to first convert height in inches and feet to meters, and weight in pounds to kilograms, then use the standard metric formula for BMI. This makes it easy to collect height and weight in commonly used units (in the United States), and also shows the metric amounts used in the calculation.
The main challenge in this example is that most people in the United States still use the US customary measurement system to record height and weight, not the metric system. The first step, therefore, is to capture this information in commonly used units. This is done in columns B (feet) C (inches) and D (pounds).
Then, to calculate height in meters, we use the CONVERT function twice in cell F5:
=CONVERT(B5,"ft","m")+CONVERT(C5,"in","m")
The first CONVERT converts feet to meters:
=CONVERT(B5,"ft","m") // feet to meters
The second converts inches to meters:
=CONVERT(C5,"in","m") // inches to meters
Then the two values are simply added together to get a total height in meters.
To calculate weight in kilograms, we use CONVERT again in cell G5:
=CONVERT(D5,"lbm","kg") // pounds to kilograms
Finally, we are ready to apply the standard BMI formula. The formula in H5 is:
=G5/F5^2 // calculate BMI
To square height, we use Excel’s operator for exponentiation, the caret (^).
Alternatives
The formulas used above can be simplified somewhat. To calculate height, we can use a single CONVERT function like this:
=CONVERT(B5*12+C5,"in","m")
In other words, we convert feet to inches directly inside CONVERT’s number argument. When Excel evaluates the formula, this operation happens before the CONVERT function runs. Not quite as readable, but more compact.
Note: Excel’s order of operations makes it unnecessary to wrap B5*12 in parentheses.
Similarly, we could convert inches to feet inside CONVERT like this:
=CONVERT(B5+C5/12,"ft","m")
The result is the same as above. The key point is that you are free to nest other calculations directly in a function argument.
As an alternative to the caret (^), the POWER function can be used to raise to a power like this:
= G5/POWER(F5,2)
