AverageIF function?

**johnmitch38** · 06-25-2009, 05:19 AM

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?

**zbor** · 06-25-2009, 05:28 AM

=averageif(a1:a12,">0")

**johnmitch38** · 06-25-2009, 05:35 AM

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.

**DonkeyOte** · 06-25-2009, 05:37 AM

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

**daddylonglegs** · 06-25-2009, 05:38 AM

Try

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

**johnmitch38** · 06-25-2009, 05:44 AM

i cant get the above to work.

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

**DonkeyOte** · 06-25-2009, 05:46 AM

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))