Ignore Zero's in AVG COLLECT Formula

LeAndre P

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,

Kelly Moore

=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

Kelly Moore

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.

Kelly Moore

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

LeAndre P

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?

Kelly Moore

=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

LeAndre P

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, 

Kelly Moore

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.

LeAndre P

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,