+ Reply to Thread
Results 1 to 14 of 14

Average formula not working

  1. #1
    Forum Contributor
    Join Date
    05-03-2010
    Location
    scotland,uk
    MS-Off Ver
    Excel 2007
    Posts
    186

    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. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #3
    Forum Contributor
    Join Date
    05-03-2010
    Location
    scotland,uk
    MS-Off Ver
    Excel 2007
    Posts
    186

    Re: Average formula not working

    attached screen shot.
    Attached Images Attached Images

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,652

    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. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Average formula not working

    Try

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

  6. #6
    Forum Contributor
    Join Date
    05-03-2010
    Location
    scotland,uk
    MS-Off Ver
    Excel 2007
    Posts
    186

    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. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  8. #8
    Forum Contributor
    Join Date
    05-03-2010
    Location
    scotland,uk
    MS-Off Ver
    Excel 2007
    Posts
    186

    Re: Average formula not working

    Nope that also returns 44.72

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Average formula not working

    I get a result of 123.25

    Here's the file...
    Attached Files Attached Files

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #11
    Forum Contributor
    Join Date
    05-03-2010
    Location
    scotland,uk
    MS-Off Ver
    Excel 2007
    Posts
    186

    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.
    Attached Files Attached Files
    Last edited by scudo; 04-19-2016 at 04:47 PM.

  12. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #13
    Forum Contributor
    Join Date
    05-03-2010
    Location
    scotland,uk
    MS-Off Ver
    Excel 2007
    Posts
    186

    Re: Average formula not working

    Damn! I should have known that.

    Thanks Jonmo1 your solution worked.

  14. #14
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Average formula not working

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. AVERAGE (IF()) not working
    By Faridwahidi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-17-2014, 01:25 AM
  2. [SOLVED] Working out an average for the best 8
    By Cmorgan in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 07-02-2012, 05:33 AM
  3. Average Formula Not Working - Nothing Shows Up
    By Jenniferh05 in forum Excel General
    Replies: 16
    Last Post: 06-23-2012, 09:35 AM
  4. average not working
    By mtsf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-05-2011, 04:53 AM
  5. Replies: 3
    Last Post: 08-15-2010, 09:27 PM
  6. Average formula not working when I know it should
    By TheNameless122 in forum Excel General
    Replies: 3
    Last Post: 07-15-2010, 08:10 AM
  7. Working the average
    By Pjcan1 in forum Excel General
    Replies: 3
    Last Post: 11-30-2009, 10:27 AM

Bookmarks

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