DAVERAGE devide by zero error

Copper Contributor


I get the above error, and I need to know how to find out which cell is causing this problem. I changed all the values in my table to something other than 0, and I still get the error. From what I read about the function online it could be that the cell just has the wrong type like a non integer or something. Is there any way for me to see an expanded view of what the function is dividing by and which cell is causing the issue? I am reducing the range incrementally to see when it works, but it would be great if Excel has a way of pointing it out. 


1 Reply

@shazde2023 The DAVERAGE function is a database function in which the criteria argument must be a range of cells containing at least one column label and at least one cell below it with the condition to be met for that column. The column label(s) used in the criteria range MUST match a column header in the database range. For example:


DAVERAGE Function SetupDAVERAGE Function Setup


As you can see in the screenshot above, the criteria range B2:C3 includes the column label "Date" for both conditions, in order to average the "Amount" field (D5) where the "Date" is between February 1st and February 29th of the current year.


Some of the reasons DAVERAGE might return the #DIV/0! error include:

  • the column labels used in the criteria range do NOT match any column header in the database range
  • the conditions specified in the criteria range return NO matching records
  • there are NO numeric values found in the column specified in the field argument


You can use the Advanced Filter feature to double-check which records are being returned by the conditions specified in your criteria range. On the ribbon, go to Data > Advanced, then select the same database and criteria ranges and click OK:


Advanced FilterAdvanced Filter


You can then select the entire value range returned by Advanced Filter to quickly view the Average of the results, shown in the status bar at the bottom of the Excel window:


Advanced Filter ResultsAdvanced Filter Results


If Advanced Filter returns no records, it means the conditions you've specified did not find a match, or you've made a mistake in the setup of the criteria range.


For more information, please see:


Advanced Filterhttps://support.microsoft.com/en-us/office/filter-by-using-advanced-criteria-4c9222fe-8529-4cd7-a898...