#DIV/0 WHEN THERE ARE VALUES IN EXCEL

[ursis4]

Hello, I have a database with several sensor values, I needed to take the average every N rows of the whole table that are like six columns, I have taken this average column by column since I did not know how else to do it, first use this formula RESIDUAL (ROW (A2); 12) and then IF (RESIDUAL (ROW (A2); 12) = 0; AVERAGE (OFFSET (A2 ;;; - 12)); "N"), the problem has arrived when this value -3.7055e-320 repeats so I get the error # Div / 0
Does anyone know how to fix it?

 

[jasonb75]

when this value -3.7055e-320 repeats so I get the error # Div / 0

If that is your actual value then it is below the absolute minimum value that excel will recognise and as such will be treated as a text string with no numeric value by any formula that is referring to it. Depending on the functions used that either means a #VALUE! error, or, in the case of the average function, it will have a value of 0 resulting in the #DIV/0 errors that you are seeing.

See the section on Calculation specifications and limits by following the link below for the largest and smallest numeric values that excel will recognise. If you need to work with values outside of that range then you will likely need something other than excel for the task.

Excel specifications and limits - Microsoft Support

In Excel 2010, the maximum worksheet size is 1,048,576 rows by 16,384 columns. In this article, find all workbook, worksheet, and feature specifications and limits.

support.microsoft.com