Problem with average

[Moperk23]

I have 3 sections in this spreadsheet. Leads, Sales and Success Rate.
Success rate = Sales/Leads. The data is for Jan-Dec. When someone has zero
leads and zero sales I end up with a /0 error.

The problem is as I average the success rates together if there is an error
in a cell it doesnt count that cell. So if Jan is 50%, Feb is 0% and March
gives an error, the average is 25%. But it should be (.5 +0+0)/3 How do I
make the error cell equal to zero for average to be correct?

I am already using a condition function to make the error cell read as 0%
for the average purpose it treats it as an error. Please help

 

[Gary''s Student]

This is one case in which you can have your choise. Say we have cells in
column C like:

=A1/B1
=A2/B2
=A3/B3

and we have another cell with =AVERAGE(C:C)

clearly if B3 is zero, C3 will show and error and AVERAGE() is impacted.


If we want to exclude errors, then in C3:
=IF(ISERROR(A3/B3),"",A3/B3)

If we want to include errors as zero, then in C3:
=IF(ISERROR(A3/B3),0,A3/B3)

 

[Fleone]

Moperk23,
Let's assume that column A is Leads, Column B is Sales, and Column C is
Success Rate. Place this formula in Column C and it will resolve your problem.
=IF(A1=0,0,B1/A1)
This says "If Leads are zero, then show a 0, otherwise divide sales by leads
and show the result"
Now with a zero in place of your div/0 error, your average will work.