A list of sales reps and sales amounts. Which ones of sales reps have sales amount higher than average sales.

One way is to write an IF sentence.

Sentence in D3 is:

=IF(C3=(AVERAGE($C$3:$C$12));”ave”;IF(C3>(AVERAGE($C$3:$C$12));”above”;”below”))

If value is equal to average value, then the result is ave, if value is not average and if the value is above average then the result is above, if the value is not in average and it is below the average, then the result is below.

The conditional formatting has also feature above and below average. If you select the data range C3:C12 and select home – conditional formatting – top/bottom rules – above average, then the values higher than average are highlighted.

The cells above the average are coloured. The same cells are highlighted as with IF sentence.

You can count the values above average with MS Access.

Table Ave was created.

The table consists of sales rep and sales amount. ID field is a running number.

Same data is entered as in Excel was keyed in Access.

An SQL query is written to detect the values below and above average. The logic is same than in Excel. In Access if is IIF.

SELECT sales,

IIF(sales = (SELECT AVG(sales) FROM ave), ‘avg’, IIF(sales > (SELECT AVG(sales) FROM ave), ‘above’, ‘below’))

FROM ave;

The results are the same as in Excel.

When comparing these three methods, I think that conditional formatting is the easiest and fastest way to calculate which values are above or below average. No manual logic is needed when conditional formatting is used. Normally, Access is not used for calculation like this, but SQL is a very powerful tool for many analytical tasks.