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

%3CLINGO-SUB%20id%3D%22lingo-sub-2198026%22%20slang%3D%22en-US%22%3EHow%20to%20change%20%22%23DIV%2F0!%22%20into%200%20in%20my%20average%20data%20so%20I%20can%20make%20descriptive%20statistics.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2198026%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20lot%20of%20data%20with%20a%20period%20of%20time%20(monthly%20from%202019-2021)%2C%20%22speed%20data%22%2C%20and%20%22stations%20data%22.%20Most%20of%20each%20station%20has%20monthly%20data%20from%202019-2021%2C%20but%20there%20is%20ONE%20station%20that%20only%20for%202019.%20It%20doesn't%20have%20any%20data%20from%202020-2021%20AT%20ALL.%20That%20means%20when%20I%20create%20the%20average%20speed%20data%2C%20it%20shows%20me%20%22%23DIV%2F0!%22%20because%20it%20doesn't%20show%20any%20data.%20For%20total%20monthly%20in%20each%20station%2C%20it%20only%20shows%200.%20So%2C%20how%20can%20I%20change%20%22DIV%2F0!%22%20into%200%20in%20my%20average%20formula%2C%20because%20I%20can't%20create%20the%20descriptive%20statistics%20because%20of%20this.%20What%20formula%20can%20I%20use%20for%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20original%20formula%20to%20find%20the%20average%3A%26nbsp%3B%3DAVERAGEIFS(SpeedData%2CStationsData%2CV199%2CHighwayData%2CR199%2CYearData%2CS199%2CMonthData%2CT199)%3C%2FP%3E%3CP%3EThank%20you!%20I%20appreciate%20any%20help.%20Hope%20that%20makes%20sense.%20Let%20me%20know%20if%20you%20still%20confused.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2198026%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New 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.