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?