# AVERAGEIF Formula Different then Excel Average

1. ## 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  Register To Reply

2. ## 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.  Register To Reply

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

4. ## 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")  Register To Reply

5. ## 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  Register To Reply

6. ## Re: AVERAGEIF Formula Different then Excel Average Originally Posted by Winfield 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  Register To Reply

7. ## 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!  Register To Reply

8. ## 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  Register To Reply

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