Ignore Zero when Averaging (BUT not a1:a10)

I searched on "average and omit zero"

NOTE THE PART AT BOTTOM ABOUT ENTERING THE ARRAY FORMULA !!!

Worked for me....should do same for you too.

VAPCMD

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

How?

Create a blank workbook or worksheet.

Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.

In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
5
6
7

A
Data
10
7
9
27
0
4
Formula Description (Result)
=AVERAGE(IF(A2:A7<>0, A2:A7,"")) Averages the numbers in the list except those that contain zero, such as cell A6 (11.4)

*************Note: The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the cell A9. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.

I've never learned the details of an array formula, so I can't comment on VAPCMD's solution.

Let me comment that I don't understand the problem quite well. It's not an average of 10 consecutive cells. It's the average of 7 non-consecutive cells. Or is it? That would be simple: just list all 7 cells as a parameter: =average(d6,d12,d18,d24,d30,d36,d42).

Or do you mean that some of that 7 cells contain 0 and those shouldn't be included. If only d6 and d24 happen to contain real prices, you would want the average of those two cells only. So the average of (4,0,0,3,0,0,0) would be 3.5, not 1.0.

As I said, I've never managed to understand array formulas. What I would do: in column E make formulas like =IF(D1=0;"";D1). Copy that down. This makes E6 equal to 4, E12 equal to an zero-length string. A zero-length string (any string in fact) is left out of account. That would make the formula =average(e6,e12,e18,e24,e30,e36,e42) return 3.5.

Kees

A formula like ==SUM(inputrange)/COUNTIF(inputrange,">0") should do the trick, the denominator of the mean is only the cells > than 0. This assumes that there are no cells <0 that you want included.

I have a question about those averages.

Are you sure that the zero figure cells should not be included in the averaging? Only, 0 is a valid number, and is also an indication of the status of the series.

What I mean is this. Say you have 10 weeks worth of sales, A1 to A10 shows the weekly sales;

A1 = $27
A2 = $451
A3 = $0
A4 = $98
A5 = $218
A6 = $0
A7 = $63
A8 = $71
A9 = $5
A10= $83

Over those 10 weeks, the 'average' sales per week is $1016 / 10, or $101.60
eg: The statement would read;
"Over the period 6/1/09 to 8/9/09 the average weekly sales were $101.60".

If you remove the zero weeks, then the average over the '8' weeks becomes $127.00
eg: The statement would read;
"Over the period 6/1/09 to 8/9/09 the average weekly sales were $127.60".

Unless you clearly state that the averaging does not include weeks where no sales were made, does that not give a false impression? That isn't what averaging is normally about.

Just a thought, and of course the final decision is yours.

Mark

{Note: Although in Europe myself, I used the US date convention. For me they would be 1/6/09 to 9/8/09, }.