There are times when estimating numbers that an average is a good message to use.

There are other times, especially when you have missing or zero data, that AVERAGE doesn’t help much.

Let’s say you have the results of four stock takes over the year. You want to average the four counts to get a value to use in a stock turn calculation. Possibly the last count was affected by a short-term seasonal jump in demand so you didn’t want to use just that count.

In the image below we have a few products and four counts. The average is in column F.

In Product 1 the average works well as there is stock in all four counts.

Products 2 to 5 are not so good. Let’s say we want to perform an average that ignores zeroes. The AVERAGEIF function can do just that.

In the image below column G has the formulas, column H shows the formulas from column G.

The AVERAGEIF function ignores the zeros and only averages numbers above zero. The formula in cell G2 is

=IFERROR(AVERAGEIF(B2:E2,">0"),0)

You need to include the IFERROR function to avoid an error when all entries are zero like Product 4.

Blank cells

One way for the AVERAGE and AVERAGEIF function to return the same results is to use blank cells instead of zeroes.

The image below has an example.

Pic 3

The AVERAGE function ignores blank cells.