+ Reply to Thread
Results 1 to 3 of 3

Different results for =average & averageifs for same data range

  1. #1
    Registered User
    Join Date
    02-20-2017
    Location
    Maine
    MS-Off Ver
    excel 10
    Posts
    22

    Different results for =average & averageifs for same data range

    Moderators, if this is in the wrong place, or is in any way of not being in compliance, please let me know and I will gladly make any corrections that I need too. I value the forum and wish to do thing correctly.

    I have a large column of numbers, sorted by date, that i used to get the average by manually averaging the date range that I needed.

    I thought I could use an averageifs with date ranges but when I do, the answers differ (though they are correct on some ranges.)

    In the spread sheet attachment I get an answer of 467.95 for a manual average, and 472.37 when I use averageifs. (=AVERAGEIFS(B:B,A:A,">="&$F$5,A:A,"<="&$F$6))

    I am a novice and most likely making a simple error, but I cannot locate anything out of place.

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Different results for =average & averageifs for same data range

    The problem occurs because you column A is Date and Time so the comparison of DATE-only vs Date & Time will give erroneous results.

    Insert a new column (B) and in B2

    =INT(A2) copy down which give Date only

    change your AVERAGEiFS

    =AVERAGEIFS(C:C,B:B,">="&$F$5,B:B,"<="&$F$6)

    and you will the same result as the C2:C23 AVERAGE

  3. #3
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Different results for =average & averageifs for same data range

    You forgot to take time into consideration.

    01/07/2017 doesn't include 01/07/2017 03:05pm since time adds value too. Change your formula to =AVERAGEIFS(B:B,A:A,">="&$E$5,A:A,"<="&$E$6+1)

+ 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. [SOLVED] AverageIfs to average two ranges if match is met
    By bjnockle in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-20-2017, 04:25 AM
  2. [SOLVED] AVERAGEIFS to average times in a range if criteria met
    By bjnockle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-26-2016, 03:06 AM
  3. [SOLVED] AVerageifs a range of data
    By kent97 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-05-2016, 06:08 AM
  4. [SOLVED] Averageifs and rolling data range
    By Wolfgang17 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-24-2014, 07:40 AM
  5. [SOLVED] How to get an AVERAGEIFS formula to not include cell in average, by triggering switch?
    By Carmstrong227 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-19-2013, 01:15 PM
  6. Averageifs - based on multiple dates average time values
    By gavinp in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-20-2013, 04:32 PM
  7. Using AVERAGEIFS function to average a smaller subset of criteria data
    By RJS5062 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-22-2012, 04:19 PM

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