How to change "#DIV/0!" into 0 in my average data so I can make descriptive statistics.

Copper Contributor

I have a lot of data with a period of time (monthly from 2019-2021), "speed data", and "stations data". Most of each station has monthly data from 2019-2021, but there is ONE station that only for 2019. It doesn't have any data from 2020-2021 AT ALL. That means when I create the average speed data, it shows me "#DIV/0!" because it doesn't show any data. For total monthly in each station, it only shows 0. So, how can I change "DIV/0!" into 0 in my average formula, because I can't create the descriptive statistics because of this. What formula can I use for this? 

This is my original formula to find the average: =AVERAGEIFS(SpeedData,StationsData,V199,HighwayData,R199,YearData,S199,MonthData,T199)

Thank you! I appreciate any help. Hope that makes sense. Let me know if you still confused.

2 Replies

@Frutank To get rid of any error code, wrap your formula in an IFFERROR statement. Something like this:

=IFERROR(<your formula>,"")

This will result in a blank if the formula returns an error. Replace the "" with anything you would want to display in case of an error, for instance, 0 (zero) or "Not applicable".

You mentioned that you are calculating averages. Note the that a series of numbers like 10, 0, 20 will average to 10, whereas 10, blank, 20 will average to 15.