Excel: Compute statistics (like an average) of all non-zero data

I have an array of data that contains some zeros at the end (though I'd like NOT to assume they are at the end), and compute stats on those which are non zero. I'd like NOT to use Ctrl-Shift-Enter formulas (which are all but maintainable) and be able to use any stats function (like QUARTILE) instead of recoding the average function by SUM()/COUNT().

Here is an example to make it clearer: my data in A1:A10 contains (1,2,3,4,5,6,7,8,0,0). I want to get the quartiles, just like below, but dynamically computing the "A1:A8"

``````=QUARTILE(A1:A8, n) // ignoring the zeros
``````

Of course I could achieve that by assuming the zeros are at the end only by building the first argument for QUARTILE() with

``````"A1" & ":" & ADDRESS( x + COUNTIF(A1:A10; "<>0") )
``````

but again, I'd like to be able to have zeros inside my array, not only at the end.

Or will I have to dive deep into VBA?

-
Can you sort the data so all of the 0s are at the start or end? – shufler Jul 10 '12 at 16:01
@shufler: I could make that there are no zero inside, but that would require a little job (end will be less elegant to solve the problem). The source for this data is "The number of rows in another sheet bearing current date": obviously zero in the future, but it requires a little trick to not have zero-records days – PPC Jul 10 '12 at 17:57

Some of the functions may be replaced by `IF` or `IFS` variants e.g. `SUMIF`, `SUMIFS`

e.g. a MEAN may be calculated using `=SUMIF(A1:A10,"<>0")/COUNTIF(A1:A10,"<>0")`
`SUMIFS` can use more than 1 criteria, so may lend itself to more complex queries

A list of the functions that can use selection criteria in their calculations:

• AVERAGEIF
• AVERAGEIFS
• SUMIF
• SUMIFS
• COUNTIF

The other approach would be to use a helper column, that has `=IF(A1=0,"",A1)` so that blanks replace the zeros, and so will not affect the statistical functions

-
Often the simplest solution is the best: the "empty instead of zeros" work perfectly – PPC Jul 10 '12 at 18:03
Don't get the point with SUMIF: can you develop? – PPC Jul 10 '12 at 18:04