Excel order tricks…

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

excel1

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.

  1. 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
  2. Copy the formula down the column—the rank or relative position against the B3:B15 cell range—will be displayed.

excel2

 

Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s