Solved How to eliminate #DIV/0! error

Dell / Inspiron 17r
June 1, 2020 at 07:11:36
Specs: windows 10, 2.3/8
I am using an Excel spreadsheet to keep track of my bicycle riding. In column C, I am using c = counta(I8:AM8) which returns the number of days I rode in a given month. In column F, I am using f = sum(I8:AM8) which returns the number of miles I rode in a given month. In column G, I am using g = c (row number)/f (row number) which returns the average number of miles rode in that month. This last formula works fine for those months that have data but for those months that have no data, i.e., July, it returns #DIV/0!. I know this error is caused by division using 0. Is there a way of writing a formula that would return "0.00" instead of the error message?

Thank you.
Brian W


See More: How to eliminate #DIV/0! error

Reply ↓  Report •

#1
June 1, 2020 at 07:37:21
✔ Best Answer
Look at the IFERROR function:

=IFERROR(your formula, 0)

message edited by DerbyDad03


Reply ↓  Report •

#2
June 1, 2020 at 08:03:08
DerbyDad03,

Thank for your your response. When I enter in column G, row 10, =iferror(c10/f10,0), I get 0.00. When I enter data, I get the correct return for column C and for column but column G stays at 0.00. If I enter =iferror(=c10/f10,0), I get an error message. What am I doing wrong?

Thanks again,
Brian W


Reply ↓  Report •

#3
June 1, 2020 at 09:46:26
re: =iferror(=c10/f10,0)

This will cause an error because the extra = sign. A nested formula does not require the leading = sign.

re: I get the correct return for column C and for column but column G stays at 0.00. If I enter =iferror(=c10/f10,0), I get an error message.

What does this mean?

"correct return for column C and for column but column G stays at 0.00"

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
June 1, 2020 at 09:51:32
P.S. Please use standard cell references in your posts whenever possible.

Instead of "column G, row 10" just use G10.

Standard references are easier to read.

message edited by DerbyDad03


Reply ↓  Report •

#5
June 1, 2020 at 10:00:59
DerbyDad03,

Thank you for your assistance. Now knowing that =iferror was needed, I tried different formulae until I got the one that worked. For example for row 10, I used =iferror(sum(I10:AM10)/counta(I10:AM10),0) and it worked correctly.

Thanks again,
Brian w


Reply ↓  Report •

#6
June 1, 2020 at 10:34:59
I'm glad you got it working.

2 thoughts:

1 - If the IFERROR was still returning 0.00, then there must have been an error caused by the formula you were using. You could have (should have?) just stripped off the IFERROR function as a means to troubleshoot the actual error. It probably wasn't a #DIV/0! error if you are sure that the values in the referenced cells were correct.That's the one drawback of the IFERROR function: It hides the actual error so you don't really know what the error is.

The old fashion, tried and true method of testing the actual value would eliminate the #DIV/0! error yet reveal any other type of error.

=IF(F10=0,0,C10/F10)

When using IFERROR you have to be confident that the error you are trying to handle is the only error that can occur. For example, if you accidentally typed this into the formula bar, you would get 0.00 even if the data in Columns C and F were correct:

=IFERROR(SUMM(I10:AM10)/COUNTA(I10:AM10),0)

The use of SUMM will cause a #NAME? error but the IFERROR will capture it and return 0.00 even if the referenced data is fine.

2 - Similar to using proper cell references in your posts (e.g. G10) it's better if you copy/paste your formulas from the spreadsheet into your post. There is less chance of a posting error than when you manually type in the formula. For example, I know that you don't have the following formula in your sheet:

=iferror(sum(I10:AM10)/counta(I10:AM10),0)

What you actually have is:

=IFERROR(SUM(I10:AM10)/COUNTA(I10:AM10),0)

message edited by DerbyDad03


Reply ↓  Report •

#7
June 1, 2020 at 11:24:53
DerbyDad03,

Thank you for your assistance; I would never have known that =IFERROR was the correct formula to use.

Brian W


Reply ↓  Report •

Ask Question