# Different results for =average & averageifs for same data range

1. ## 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

2. ## 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

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

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

3. ## 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)

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