Don't count null value in functions

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

ARZ -37.7 -14.5 -49.5 29 -32.5 -30 -7
40 27 -30 -181.5 24 -143.5 -
191.25 32 0 0

I'm using a weighted formula for weekly calculations. I
then want to get the average of all the values except 0.
How do I get the AVERAGE formula not to count zeroes in
the string? Such as the formula would be AVERAGE(A1:L1),
if there are zeroes in K1 and L1, how can I get the
average of just A1 through J1? Thanks for any help, I'm
stuck!
 
One way

=AVERAGE(IF(A1:L1<>0,A1:L1))

entered with ctrl + shift & enter

if all cells are empty it will return an error, you could dodge that

=IF(COUNTBLANK(A1:L1)=COLUMNS(A1:L1),"",AVERAGE(IF(A1:L1<>0,A1:L1)))
 
I am using a range made up of selected cells. I am trying to averag
the cells that aren't zero.

{=AVERAGE(IF(Qtr_Out>0,Qtr_Out))}

I am getting an error. The range is a selection of 12 cells that ar
non-contiguous. Qtr_Out consists of G5, K5, M5, etc...

Any suggestions
 
savarin said:
I am using a range made up of selected cells. I am trying to average
the cells that aren't zero.

{=AVERAGE(IF(Qtr_Out>0,Qtr_Out))}

I am getting an error. The range is a selection of 12 cells that are
non-contiguous. Qtr_Out consists of G5, K5, M5, etc...

Any suggestions?
Click to expand...

You can't use multiple area ranges in array functions. You could hack this
by using OFFSET to generate an array of references to the required cells.
For example,

=AVERAGE(IF(N(OFFSET(A1:F1,0,{0,2,4},1,1))>0,N(OFFSET(A1:F1,0,{0,2,4},1,1)))
)

would return the average of the positive cells A1, C1 and E1. Alternatively,
you could use INDIRECT.

=AVERAGE(IF(N(INDIRECT({"A1","C1","E1"}))>0,N(INDIRECT({"A1","C1","E1"}))))

Note that wrapping the OFFSET or INDIRECT calls inside N() is mandatory.
 
Back
Top