Excel Average Formula

By Johnnyblade65 ·
Hi all, I am trying to create an average formula that does not include any cells that have a value of zero. I know I can use AVERAGEIF() for a range of cells but I need it for a selection of individual cells.

I am using Excel 2010

Can anybody help?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

AVERAGEIF() is the way to go...

by Sven2157 In reply to Excel Average Formula

Using AVERAGEIF() gives you the control that you are in need of; ability to average the cells, coupled with the ability to selectively add cells that meet your criteria.

The Function Explained:
AVERAGEIF( cell_range, criteria, average_cell_range )

cell_range - Simply put, which cells do you want to measure( INCLUDING the ones with zeros! ))? This includes letters, numbers, etc, etc...
criteria - Here is where you specifically 'ween-out' the values of zero.
average_cell_range - This is the actual set of cells to use. If left out, range is used.

Now for your situation( from the vague information you have provided ), you could simply use the following:

AVERAGEIF( A2:A50, ">0" )

This would return the average, between cells A2 and A50, that are greater than zero( 1 or more ).

Unless I missed something, that would be the easiest way.

Collapse -

Reponse To Answer

by Johnnyblade65 In reply to AVERAGEIF() is the way to ...

Thanks for the reply, I might not have been specific enough in my question. I need to average cells that are not in a consecutive range; ie. cell refs, A5, A7, A15, A28 and not any in between.

Collapse -

Reponse To Answer

by Sven2157 In reply to AVERAGEIF() is the way to ...

With the new information you have provided, it is still my opinion that you want the AVERAGEIF(), possibly the AVERAGEIFS(), function. However, you will need to use another cell group to average the specified cells.

I assume that the cells you want to average are of some type of group; or have something in common? If so, then the 3rd criteria is used here:

AVERAGEIF( A2:A50, ">0", B2 )

Again, without more information about your spreadsheet, it is difficult to say what you need to do exactly. Have a look at this tutorial, as he explains very clearly and precisely the differences of these functions.

Danny Rocks' "Use Average, AverageIf and AverageIfs Functions in Excel"

Related Discussions

Related Forums