Ignore Zero's in AVG COLLECT Formula

Options
LeAndre P
LeAndre P ✭✭
edited 06/27/22 in Formulas and Functions

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    =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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    =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
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!