Ignore Zero's in AVG COLLECT Formula
Hello,
I am using the formula below to return the average value of a column when certain criteria is met. The formula works however, I have discovered it is considering cells with zeros consequently, skewing the true average total. I’ve tried adding ISBLANK and @row to no avail. How do I fix this?
=IFERROR(AVG(COLLECT({POTotal}, {ClaimDate}, >=DATE(2021, 1, 1), {ClaimDate}, <=DATE(2021, 12, 31))), 0)
Thank you,
Best Answers
-
=IFERROR(AVG(COLLECT({POTotal},{ClaimDate}, ISDATE(@cell), {POTotal}, >0,{ClaimDate}, >=DATE(2021, 1, 1), {ClaimDate}, <=DATE(2021, 12, 31))),0)
This will filter your non-zeroes out of the data set. It will make no difference if the zeroes are legit, they will be ignored. In your source sheet, can you apply a quick filter to see the zero values - maybe there is a different condition that will filter your zeroes that are there without cause.
Let me know and I'll try to help
Kelly
-
When you swapped sheets, did you rebuild each of the cross sheet references manually? Cross sheet references can't be copy pasted to different sheets.
Answers
-
Try this
=IFERROR(AVG(COLLECT({POTotal},{ClaimDate}, ISDATE(@cell), {ClaimDate}, >=DATE(2021, 1, 1), {ClaimDate}, <=DATE(2021, 12, 31))),0)
As you are looking at the entire year, you may find the YEAR function to be a useful alternative.
=IFERROR(AVG(COLLECT({POTotal},{ClaimDate}, ISDATE(@cell), {ClaimDate}, YEAR(@cell)=2021)),0)
Will either of these work for you?
Kelly
-
Hi Kelly,
Thanks for assisting. Both of your suggestions worked however, the total value remained unchanged, suggesting the cells containing zeros are still being considered. Any ideas?
-
=IFERROR(AVG(COLLECT({POTotal},{ClaimDate}, ISDATE(@cell), {POTotal}, >0,{ClaimDate}, >=DATE(2021, 1, 1), {ClaimDate}, <=DATE(2021, 12, 31))),0)
This will filter your non-zeroes out of the data set. It will make no difference if the zeroes are legit, they will be ignored. In your source sheet, can you apply a quick filter to see the zero values - maybe there is a different condition that will filter your zeroes that are there without cause.
Let me know and I'll try to help
Kelly
-
Hi Kelly,
Thank you, that worked as expected. Since the shorter version is cleaner, I went with it instead.
Works with specific sheet: =IFERROR(AVG(COLLECT({PoValue}, {ClaimDate}, ISDATE(@cell), {PoValue}, >0, {ClaimDate}, YEAR(@cell) = 2021)), 0)
Although the above formula seems to be working it returns $0 when I attempted to use it with different source sheet. By and large both sheets are more or less identical having the same values and data contained within. The only changes made were to the names of the source columns that the formula was to evaluate for the desired result. Any ideas why this is happening?
Returns no result: =IFERROR(AVG(COLLECT({POLTotal}, {POLClaimDate}, ISDATE(@cell), {POLTotal}, >0, {POLClaimDate}, YEAR(@cell) = 2021)), 0)
Thank you,
-
When you swapped sheets, did you rebuild each of the cross sheet references manually? Cross sheet references can't be copy pasted to different sheets.
-
Hi Kelly,
I found the issue. It turned out that the {POLClaimDate} column settings on the new source sheet was set to Text/Number instead of Date. Once I changed the settings it returned a value. Thanks for all your help.
Regards,
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!