Forum Discussion
Solved
Trying to find the average of two cells and, when the answer is zero not get #DIV/0
I am trying to calculate an average, D4/A4, and when the answer is 0.00 to get that to display instead of #DIV/0 in the cell. Thanks in advance.
Sample:
| 1 | $ 5.00 | $ 2.00 | $ 7.00 | $ 7.00 |
| 3 | $ 15.00 | $ 6.00 | $ 21.00 | $ 7.00 |
| 5 | $ 25.00 | $ 10.00 | $ 35.00 | $ 7.00 |
| 0 | $ - | $ - | $ - | #DIV/0! |
| 4 | $ 20.00 | $ 8.00 | $ 28.00 | $ 7.00 |
| 1 | $ 5.00 | $ 2.00 | $ 7.00 | $ 7.00 |
| 1 | $ 5.00 | $ 2.00 | $ 7.00 | $ 7.00 |
| 0 | $ - | $ - | $ - | #DIV/0! |
| 0 | $ - | $ - | $ - | #DIV/0! |
- You would use this formula, in case there is an error put 0
=IFERROR(D1/A1,0)
6 Replies
- You would use this formula, in case there is an error put 0
=IFERROR(D1/A1,0)- That did it! Thank you very much. Much gratitude!!
- You are welcome
I am glad that it helps you
- Maybe my use of the word 'average' is incorrect. In this example, Column A represents the number of sales made in one department, Column B represents the total dollars sold for one item, Column C represents the total dollars sold for another item, Column D represents the sum of the value of Column B and Column C. Column E is represents Column D divided by Column A. If there were no sales, then zero is the correct answer. In Excel language, I understand why it shows #DIV/0, but I want it to present as something more aesthetic like 0.00 or -. Does that make more sense?
- Use
=IF(A4<>0, D4/A4, 0)
