# Average formula not working

1. ## Average formula not working

I obviously have something not correct in this formula.
I am trying to get an average of the cells above but only if they have a number in them, the cells represent the months of the year so I am trying to get what should be Jan - April / 4 and not Jan - April / 12.

I used google to try and work it out but I dont understand what I am doing wrong.

SUMIF(D24,F24,H24,J24,L24,N24,P24,R24,T24,V24,X24,Z24)/(SUMPRODUCT((D24,F24,H24,J24,L24,N24,P24,R24,T24,V24,X24,Z24<>0)*1))

2. ## Re: Average formula not working

What is in the between cells (E24 G24 I24 etc) ??
Is there a common header in another row, say 23, that indicates which columns should be included ?

Can you attach a sample file?

3. ## Re: Average formula not working

attached screen shot.

4. ## Re: Average formula not working

Try

=SUM(D24,F24,H24,J24,L24,N24,P24,R24,T24,V24,X24,Z24)/COUNT(D24,F24,H24,J24,L24,N24,P24,R24,T24,V24,X24,Z24)

Will ignore blank cells

5. ## Re: Average formula not working

Try

=AVERAGEIF(\$C\$16:\$Z\$16,"<>sales",\$C\$24:\$Z\$24)

6. ## Re: Average formula not working

JohnTopley, that gives the wrong answer Giving £44.72 should be £123 for the average of the 4 months.

Jonmo1, That also gives the wrong answer.

The 4 cells that with amounts are: £56, £94, £138, £205
Total = £493. Average = £123.25

7. ## Re: Average formula not working

Maybe this array formula**:

=AVERAGE(IF(MOD(COLUMN(D24:Z24)-COLUMN(D24),2)=0,IF(ISNUMBER(D24:Z24),D24:Z24)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

8. ## Re: Average formula not working

Nope that also returns £44.72

9. ## Re: Average formula not working

I get a result of 123.25

Here's the file...

10. ## Re: Average formula not working

Can you attach an actual Excel File ?
Pictures tend to not be helpful because we can't copy/paste the data into our own workbooks for troubleshooting.

11. ## Re: Average formula not working

Out of interest I have attached the file showing my result. The result is in cell AI6.

I will now download yours and compare, probably something I have done wrong.

I will report back later.

I should add that cell J24 has changed slightly as I updated it, just in case you notice slight difference in the total.

Jonmo, Yes now attached.

12. ## Re: Average formula not working

You're getting the wrong value from all of these suggestions because those cells in row 24 that look blank, are NOT blank.
They contain SUM formulas that are returning 0's.
But you have 0's hidden because you UNchecked File - Options -Advanced - "Show a zero in cells with zero value"

Those cells STILL actually contain a 0 (it's just hidden, but it's still there)
0 Is a real value and will be counted in the Average.

Try
=AVERAGEIFS(\$C\$24:\$Z\$24,\$C\$24:\$Z\$24,"<>0",\$C\$16:\$Z\$16,"<>sales")

13. ## Re: Average formula not working

Damn! I should have known that.

14. ## Re: Average formula not working

You're welcome.

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