AVG(COLLECT 0%

Mike Arbaugh

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

Mike Arbaugh

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

Paul Newcome

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

Mike Arbaugh

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

@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

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

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