Average in Excel

The AVERAGE function is not the only function in Excel for calculating
averages, as there are several kinds of averages and each is good for
something else. The goal of this guide is to show you specific
functions for calculating averages on examples for which they are most
suitable. Not every one of us is a mathematician, or has the
foundations of statistics, which is why I will try to make these
instructions as easy to understand as possible. The instructions assume
basic knowledge of creating formulas
in Excel.

Average
in Excel

All of us surely remember arithmetic average from school. In
Excel, we write the AVERAGE function in the following format:

= AVERAGE
(data range 1 , data range 2......30)

The data range can be defined by cell coordinates (e.g.,
A1:A10) or by the name of the identified range (labelled column, row or
a dynamically labelled range). There is nothing complicated about
averages, so we’ll start with an example right away:

The boss asked us to determine the average income of the store’s
customers. Enter the following table with income figures into Excel and
save it (we will work with it):

Using Excel’s AVERAGE function, the solution is more than
simple. All you have to do is enter the formula in the following format
into cell D2, for example:

=AVERAGE(A1:A6)

The function’s result will not surprise anybody:

The average income of the store’s customers is 3,500. We can
be satisfied, and the boss as well. But life is not simple enough for
the AVERAGE function to be all we need. The average is only used in a
data file in which there are minimal differences. The following chapter
discusses data files with large differences.

Median
in Excel

The MEDIAN function calculates the middle value of the
numbers. In Excel, we write it in the following format.

= MEDIAN
(data range 1 , data range 2......30)

We will best understand the invaluableness of the MEDIAN function on a
clear example. The example will be the same as for the AVERAGE
function, but the input data will be different (input it according to
the picture below):

We changed data in cells A1 and A6. This caused the customers’
average income to go up. The average is now 12,333. If we were to base
our business decisions on the average, then we would undoubtedly go
bankrupt very soon. The explanation is simple. We set the prices for an
“average” customer, but there are only few “average” customers in the
group. We are looking at six income figures, but only one is average or
higher. Such an approach is bad. We can correct it by using the MEDIAN
function. We write it into cell D3 in the following format:

=MEDIAN(A1:A6)

The result of the median is 3,500. Three customers in the group have a
higher or same income. Three customers are more than one:

The median value is suddenly the same as the average before
the change of the input data. From this we can learn that the median is
used for data files that have large differences (that is why we put 1
into cell A1 and 60,000 into cell A6). Statisticians call this the
variation range.

Range
of variation in Excel

Our data file is short, therefore we were able to estimate the
variation range easily. A different situation occurs when there is much
more data. In this case it is useful to calculate the variation range.
We will need two functions for this:

MAX function - in the data file finds the cell with the
highest value (in our example it will find cell A6 because it has a
value of 60,000)

MIN function - in the data file finds the cell with the
lowest value (in our example it will find cell A1 because it has a
value of 1)

Writing the MAX and MIN functions is the same as the AVERAGE or MEDIAN
functions:

= MAX
(data range 1 , data range 2......30)

In cell D4 we enter the MAX function in the following format:

=MAX(A1:A6)

In cell D5 we enter the MIN function in the following format:

=MIN(A1:A6)

And finally we calculate the variation range by simply subtracting the
lowest value from the highest one. We write the formula into cell D6.

=D4-D5

This completes our simple statistical analysis, and it didn’t even
hurt. We have all data necessary for our decision on one sheet:

We clearly see what the highest and lowest value in the file
is. We also see the variation range, which tells us that there are huge
differences in the data file (even taking the lowest and highest value
into account). We therefore use the median instead of the average.