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.

Can you sort the data so all of the 0s are at the start or end?
–
shuflerJul 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
–
PPCJul 10 '12 at 17:57