# AverageIF function?

1. ## AverageIF function?

I want an average of 5 different cells but only i only want it to include the cell if the number in the cell is higher than 0.

The cells i want to average are populated automatically and default to zero.

Is there a way i can do this?  Register To Reply

2. ## Re: AverageIF function?

=averageif(a1:a12,">0")  Register To Reply

3. ## Re: AverageIF function?

sorry i should have said, my cells arent next to each other so if i do an averageif i get the "too many arguments" error.  Register To Reply

4. ## Re: AverageIF function?

Is there a pattern to the location of the 5 cells... ie every xth column etc... ? ie - what are the cell locations ?  Register To Reply

5. ## Re: AverageIF function?

Try

=SUM(A1,D1,G1,J1,M1)/MAX(1,(A1>0)+(D1>0)+(G1>0)+(J1>0)+(M1>0))  Register To Reply

6. ## Re: AverageIF function?

i cant get the above to work.

My cells are in every second column i.e B6, D6, F6, H6, etc  Register To Reply

7. ## Re: AverageIF function?

So another alternative might be

=AVERAGE(IF((MOD(COLUMN(B6:J6),2)=0)*(B6:J6>0),B6:J6))
committed with CTRL + SHIFT + ENTER

But the below works fine for me...

=SUM(B6,D6,F6,H6,J6)/MAX(1,(B6>0)+(D6>0)+(F6>0)+(H6>0)+(J6>0))  Register To Reply

8. ## Re: AverageIF function? Originally Posted by johnmitch38 i cant get the above to work.
Did you get an error or the wrong result?

What's in C6, E6 etc. do those contain numbers  Register To Reply

9. ## Re: AverageIF function?

I must have done something wrong before.

Works a treat now, thanks alot.  Register To Reply