Software

Ask something
Question Locked

Excel Average Formula

0 helpful
4 replies

by johnnyblade65

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

4 replies

by sven2157

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.

[b][u]The Function Explained[/u][/b]:
AVERAGEIF( [b]cell_range[/b], [b]criteria[/b], [b]average_cell_range[/b] )

[b][u]cell_range[/u][/b] - Simply put, which cells do you want to measure( INCLUDING the ones with zeros! ))? This includes letters, numbers, etc, etc...
[b][u]criteria[/u][/b] - Here is where you specifically 'ween-out' the values of zero.
[b][u]average_cell_range[/u][/b] - 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:

[b]AVERAGEIF( A2:A50, ">0" )[/b]

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.

by johnnyblade65

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.

by sven2157

With the new information you have provided, it is still my opinion that you want the AVERAGEIF(), possibly the AVERAGEIF[b]S[/b](), 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:

[b]AVERAGEIF( A2:A50, ">0", [u][i]B2[/i][/u] )[/b]

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

[url=https://www.youtube.com/watch?v=KP06eDpI7tk]Danny Rocks' "Use Average, AverageIf and AverageIfs Functions in Excel"[/url]

This discussion is closed to new replies.