In the lead up to Halloween, nothing too spooky – just a few tricks and treats you might like to try.
You all know how to sort data, but are you aware of some other features that you could use when you want to show how your data is placed within a range? If not, try these!
IF, MAX, MIN Function
Useful when you have a list of data and you want to be able to identify the highest and lowest value in the list (without sorting)
1. Enter (or calculate) your list of values – see column B below
To display the message required e.g. Lowest Temperature/Highest Temperature
2. Enter the formula
=IF(B3=MAX($B$3:$B$15),”Highest Temperature”,IF(B3=MIN($B$3:$B$15),”Lowest Temperature”,””)) – see column C
3. Copy the formula down the column – the appropriate message should display beside the maximum and minimum values
Conditional Formatting
Alternatively, rather than display a message, you might prefer to use formatting to indicate which values are highest and lowest.
The image above shows demonstrates the highest and lowest temperature cells formatted with a background colour to make them stand out (cells B6 and B8 above).
See Conditional Formatting – highlight highest and lowest values
Or you could follow the steps at http://www.techonthenet.com/excel/questions/cond_format3_2010.php
RANK Function
If you would prefer to be able to rank the data in a range e.g. 1, 2, 3, etc, use the RANK function. In this example we are working in Column D.
- In cell D3 enter the function =RANK(B3,$B$3:$B$15) – where B3 is the cell you want evaluated, and B3:B15 is the range you want it ranked against
- Copy the formula down the column—the rank or relative position against the B3:B15 cell range—will be displayed.
InformS 