+ Reply to Thread
Results 1 to 8 of 8

AVERAGEIF Formula Different then Excel Average

  1. #1
    Registered User
    Join Date
    03-04-2013
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    3

    AVERAGEIF Formula Different then Excel Average

    Hello,

    I'm trying to figure out why my "AVERAGEIF" formula in A36, is coming up with a different average from what I get when I "ctrl select" the same cells.

    Please let me know if you need more detail.
    Dakota

    Problem Sheet.xlsx

  2. #2
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: AVERAGEIF Formula Different then Excel Average

    Dakota, welcome to the forum. As far as I know, Excel doesn't handle non-contiguous ranges well when averaging using a formula. I have used both formula and mathematics on your data, and each time Excel returned 3:14:33 as the average.

    Hope this helps.
    Attached Files Attached Files
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.

  3. #3
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    145

    Re: AVERAGEIF Formula Different then Excel Average

    I think it is in the way the formula captures the ranges you entered.
    I don't think it works using : between each range:

    Please Login or Register  to view this content.
    I copied the range into a separate column and got the Ctrl select value (attached).
    Attached Files Attached Files

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: AVERAGEIF Formula Different then Excel Average

    As the time values you need to exclude from the average are always in the cells below "Time" you could use this formula

    =AVERAGEIFS(A1:A31,A1:A31,">0",A2:A32,"<>time")
    Audere est facere

  5. #5
    Registered User
    Join Date
    03-04-2013
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: AVERAGEIF Formula Different then Excel Average

    BB1972 thanks for the welcome.

    Everyone, thanks for the feedback, it seems that because I have a non-contiguous range, a formula isn't going to solve this problem for me. (Daddy, I tried your formula and it didn't seem to work for me. The result I got with it was 0.13510101).

    Thanks again!
    Dakota

  6. #6
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,283

    Re: AVERAGEIF Formula Different then Excel Average

    Quote Originally Posted by Winfield View Post
    BB1972 thanks for the welcome.

    (Daddy, I tried your formula and it didn't seem to work for me. The result I got with it was 0.13510101).

    Thanks again!
    Dakota
    Well, when custom formatted as [h]:mm:ss, 0,13510101 is the same as 3:14:33 as in post #2
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  7. #7
    Registered User
    Join Date
    03-04-2013
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: AVERAGEIF Formula Different then Excel Average

    Pepe, you're right! Thanks for making sure I didn't let a good solution pass me by! Daddy, thanks for the formula, works like a charm!

  8. #8
    Registered User
    Join Date
    03-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: AVERAGEIF Formula Different then Excel Average

    I am curious to know how this works? I understand that the formula is not averaging anything that is zero or time, but how do the averages in the column not get averaged? thus falsely inflating the real average?

    <<
    Re: AVERAGEIF Formula Different then Excel Average

    As the time values you need to exclude from the average are always in the cells below "Time" you could use this formula

    =AVERAGEIFS(A1:A31,A1:A31,">0",A2:A32,"<>time")
    >>

    I have a similar issue

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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