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%3CLINGO-SUB%20id%3D%22lingo-sub-2198414%22%20slang%3D%22en-US%22%3ERe%3A%20How%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-2198414%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F992309%22%20target%3D%22_blank%22%3E%40Frutank%3C%2FA%3E%26nbsp%3BTo%20get%20rid%20of%20any%20error%20code%2C%20wrap%20your%20formula%20in%20an%20IFFERROR%20statement.%20Something%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(%3CYOUR%20formula%3D%22%22%3E%2C%22%22)%3C%2FYOUR%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20will%20result%20in%20a%20blank%20if%20the%20formula%20returns%20an%20error.%20Replace%20the%20%22%22%20with%20anything%20you%20would%20want%20to%20display%20in%20case%20of%20an%20error%2C%20for%20instance%2C%200%20(zero)%20or%20%22Not%20applicable%22.%3C%2FP%3E%3CP%3EYou%20mentioned%20that%20you%20are%20calculating%20averages.%20Note%20the%20that%20a%20series%20of%20numbers%20like%20%3CSTRONG%3E10%2C%200%2C%2020%3C%2FSTRONG%3E%20will%20average%20to%2010%2C%20whereas%20%3CSTRONG%3E10%2C%20blank%2C%2020%3C%2FSTRONG%3E%20will%20average%20to%2015.%3C%2FP%3E%3C%2FLINGO-BODY%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.