AVG(COLLECT 0%

Options
Mike Arbaugh
Mike Arbaugh ✭✭✭
edited 05/16/24 in Formulas and Functions

I have a sheet that I am using AVG(COLLECT on. I have it working base off my criterion_range. The issue that I am running into is that I have several days with 0% and then a few days with 50%. It looks like it is not taking the days with 0% into the average and I need it to. How can I fix this. Thanks

Best Answer

  • Mike Arbaugh
    Mike Arbaugh ✭✭✭
    Answer ✓
    Options

    I figured the issue out. On the sheet that I have leaving 0% if I have a Divided Error, I was using "0%" vs just using ),0) at the end of my formula. So, it was leaving it as a text vs a number. Thanks

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide some screenshots and your existing formula for context?

  • Mike Arbaugh
    Options

    Here you go Paul. The formula picks up everything I want except for when I have a 0%. Doing the AVG(Collect on the Centerline. Then I look at the month and the year. I might have 30 different entries in a month. The Percentage can go from 0% to 100%. But, it looks like when I have a zero, it does not take that into the AVG. This gives me a higher number than I should be getting.

    =IFERROR(AVG(COLLECT({EH5 Filler Wrap Up Sheet Range 4}, {EH5 Filler Wrap Up Sheet Range 2}, $Month@row, {EH5 Filler Wrap Up Sheet Range 3}, $Year@row)), "")

  • Jgorsich
    Jgorsich ✭✭✭
    Options

    @Mike Arbaugh - it is possible Paul will be able to make something out of your screenshots (he's a bit of a wizard), but when I read your question I was hoping to see a screenshot of some of your 0% values. Depending on how you are having them entered, they MAY be being seen as text and/or blank cells. If so, the avg function automatically disregards them.

    To get around it, you need to make sure they are actually showing up as numbers (right alignment is usually a good hint, but for absolute certainty just add an empty column nearby and enter a temporary avg function that spans some non-0 values and some 0 values and see if it is calculating the way you would expect). If they AREN'T, then you need to either modify how they are being entered OR change your avg function into a more manual sum()/count() function. If they ARE being entered as numbers and a local AVG function is working correctly, you may want to double check your collect() criteria - it is possible that either {EH5 Filler Wrap Up Sheet Range 2} or {EH5 Filler Wrap Up Sheet Range 3} is also blank when your percentage is 0%, which would exclude them from your collect.

  • Mike Arbaugh
    Options

    i checked to make sure its a number and shows a percentage. The other items it looks for is the month and year also has data. Thanks.

  • Mike Arbaugh
    Mike Arbaugh ✭✭✭
    Answer ✓
    Options

    I figured the issue out. On the sheet that I have leaving 0% if I have a Divided Error, I was using "0%" vs just using ),0) at the end of my formula. So, it was leaving it as a text vs a number. Thanks

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!