Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
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
1  
added info about IFS –  Sean Cheshire Jul 10 '12 at 18:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.