#DIV/0! Error - Most of the formulas work, except for a small few?!

**vkz7hxy** · 02-03-2016, 12:49 PM

Hi, reference the attached workbook...

The workbook itself is quite complex but the formula isn't so complicated so I'm not sure why a small number of sites are showing #DIV/0! errors as a result.

If you open the sheet and look at "RC.Total" tab and "RC.Mechanical" tab, Eastleigh, Reading and Jersey show this error. All other sites are as they should be.

I can't figure out what's going wrong with the formula/figures.. Can anybody sus it out? My eyes are going fuzzy looking at it....

Thanks!

**Jonmo1** · 02-03-2016, 12:57 PM

#Div/0! errors come from dividing a number by 0.
10/0 = #Div/0!

Take Y25 from your total tab for example
=Car.Days!Y25/(SUM(Car.Days!$K25,Car.Days!$O25,Car.Days!$S25,Car.Days!$W25)/SUM($K25,$O25,$S25,$W25))
It's doing 2 division operations, highlighted Red.
It appears to be the result of SUM($K25,$O25,$S25,$W25) is in fact 0.
K25 O25 S25 and W25 ALL = 0, so the SUM is 0.

The same corresponding cells on the Car.Days tab are all 0 as well.

**MrShorty** · 02-03-2016, 01:03 PM

Using the Evaluate Formula tool (https://support.office.com/en-us/art...6-a70aa409b8a7 ), I see that the sum(K44,O44...) section of the formula returns 0. This appears to be because each of these referenced cells contains the value 0. The sum of several 0's is 0. And Excel (and the rest of us who cannot afford Chuck Norris on our team) does not know how to divide by 0.

So, the answer to why you are getting the div/0 errors is fairly simple to see. The challenge will be, what should you do about it? Do you want to prevent all four of those cells from being 0? Do you need to change the formula to do something different when all four of those cells are 0?