How to fix divide by zero error?

Question

Hi all,

I'm trying to write an if statement that returns 1 if the formula equals zero and avoids the divide by zero error. I'm trying to show a quarterly percentage of event notes submitted divided by events attended. I'm having an issue when the individual attends zero events in a quarter. If the individual attends any events, it should show the result of (event notes submitted/events attended). If the individual attends zero events, it should show 1.

It should take the sum of the top row (# of notes submitted) divided by the sum of the bottom row (#of events attended). Here's as far as I've gotten:

=IF((SUM(C3:E3)/SUM(C4:E4)=0),1,(SUM(C3:E3)/SUM(C4:E4)))

Any help would be much appreciated!

Answer 1

Why not just use IFERROR?

=IFERROR(SUM(C3:E3)/SUM(C4:E4),1)

Answer 2

If you want to avoid the divide by 0 error, you need to test the denominator before you perform the division.

IF(SUM(CE:E4)=0,1,SUM(C3:E3)/SUM(C4:E4)

Why did you code 0.9 for the error case when your text says you want 1?

Answer 3

=IF(SUM(C4:E4)=0, 1, (SUM(C3:E3)/SUM(C4:E4)))