LinkBack URL
About LinkBacks
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
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
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.
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.
I think it is in the way the formula captures the ranges you entered.
I don't think it works using : between each range:
I copied the range into a separate column and got the Ctrl select value (attached).
Please Login or Register to view this content.
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
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
Well, when custom formatted as [h]:mm:ss, 0,13510101 is the same as 3:14:33 as in post #2Originally Posted by Winfield
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!
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks