# 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?

2. ## Re: AverageIF function?

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

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.

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 ?

5. ## Re: AverageIF function?

Try

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

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

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

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

9. ## Re: AverageIF function?

I must have done something wrong before.

Works a treat now, thanks alot.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1