Zero divide by Zero error

Question

Hi All, 

Example 1 

Everything is fine because there are value in all cells, even it there is a "0" value at Qty collected column.  there isn't any problem

Example 1
Fix cost:	$100		Delviery cost:	$20
Store	Qty collected	%Cost allocation	Cost allocation	Qty delivered	Deliver cost	Sub total
1	3	38%	$37.50	5	$100.00	$137.50
2	5	63%	$62.50	8	$160.00	$222.50
3	0	0%	$0.00	6	$120.00	$120.00
Gr Total						$480.00

Example 2 is where I meet with problem

Example 2
Fix cost:	$100		Delviery cost:	$20
Store	Qty collected	%Cost allocation	Cost allocation	Qty delivered	Deliver cost	total
1	0	#DIV/0!	#DIV/0!	5	$100.00	#DIV/0!
2	0	#DIV/0!	#DIV/0!	8	$160.00	#DIV/0!
3	0	#DIV/0!	#DIV/0!	6	$120.00	#DIV/0!
				Grand	Total:	#DIV/0!

Qty collected is zero, The formulas in % cost allocation and Cost allocation will returned with #DIV/0! because of 0 value. 

I could ignore it but excel won't add the total because there are #DIV/0! in between. 

In my actual excel worksheet DIV errors appear between rolls and column. how to have a return value of zero when 0 / 0 = 0?

Answer 1

Hi Patrick,

Replace the formulas in the %Cost allocation and Cost allocation  columns with a formula of the following type:

      = IfError(yourExistingFormula, 0)

If you want the results to appear blank, replace 0 with "".

===

Regards,

Norman