Simple Division formula resulting in error and should not be
I am having an issue with a simple averaging formula providing an incorrect outcome. In the example above the dollar amount in the DATA column is a result of a SIMIF formula. Additionally the results in Column4 are the results of a COUNTIFS formula. In Column 6 is a simple average formula that I wrapped in an if statement. If Column4 is 0 then the formula should run and if not I should return "Count is 0". As you can see there are 3 rows showing "Count is 0" but only 1 row, the lowest in the screenshot, has a zero in it. Why would this not calculate properly?
I also tried a simpler formula in Column6. Formula is =Data@row / [Column4]@row. For rows where Column4 is a 1, I get a "Divde by Zero" error.
Please help!
Answers

@David Klockzien Have you checked the Column type of each of the involved columns? Are you sure you aren't dealing with some funky rounding issue?
(I can reproduce the reverse of what you are seeing, namely a very small number that looks like 0 still returning a valid divisor.)
I suspect Column4 is either a not a Text/Number column or the value you see is the result of formatting or rounding. (Have you tried typing a value into that column?)
Cheers,
Help Article Resources
Categories
Check out the Formula Handbook template!