1. ## Excel (2000)

I do reporting for my group whereby I insert the total number of calls they make per day and then total them up for the week/month/quarter, etc. I have a calculation that will divide the number of calls they've placed by the number of leads they've passed that day to get the number of calls it takes to get a lead. For example:

100 calls

100/2 = 50 calls per lead

We do not get leads on a daily basis though and end up with 100/0 which does not calculate well and give you a #DIV/0 error. Is there a conditional formatting rule that says, if it cannot be divisible by 0, make it a 0? That way when we have totals for the week, we can include the 0 with the other numbers and get real data for a total and not #DIV/0.

2. ## Re: Excel (2000)

Because in Excel false=zero, the simplest way to handle this is:

3. ## Re: Excel (2000)

This still gave me the same error

4. ## Re: Excel (2000)

Hmm, what did I do wrong?

<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><tr><td align=center valign=bottom>1</td><td valign=bottom>Calls</td><td align=right valign=bottom>100</td><td align=right valign=bottom>=IF(B2,B1/B2,)

Regards
Peter

6. ## Re: Excel (2000)

Crossed in the ether!

Regards
Peter

7. ## Re: Excel (2000)

Okay, here's something else to add then. I want to apply this formula in such a way that if there is a real number in the data that it will populate that number, not a 0. The Excel book I have give this formula but leaves the cell blank if there is no number: =IF(ISERROR( (J3+J13) / J13)," ",(J3+J13) / J13). Can I put in a 0 within the " " to make sure I get a place holder if it's really 0 and a real number if there should be one there?

Also, the attached spreadsheet uses this formula but in column X under #attempts per lead, the number 21 is calulated and 20/1 should be 20, not 21?? Can't figure that one out...

THanks!

8. ## Re: Excel (2000)

The Excel book example is unnecessarily complicated for this situation. If you want to show a zero when there are no leads, use the formula Peter and I demonstrated. If instead you want it to show a blank, you can either use the original formula and custom format the cell not to show zero values, such as Format Cell, Custom, in the Type box enter "0;0;" without quotes; or you can enter the formula as

which may cause problems if other formulas depend on it. Does this help?

9. ## Re: Excel (2000)

I like the ISERROR solution because it is more clear exactly what is being done. That makes it much easier for someone else to figure it out, or even for you to figure it out when you come back to it many months latter.

You can return a zero, but how you do that depends on what you want. If you want a numeric zero that can be used in other calculations, then do NOT put it between the quotes, replace the quotes with it like this:

<pre>=IF(ISERROR( (J3+J13) / J13),0,(J3+J13) / J13).
</pre>

If you want to return a text zero, then put it between quotes.

10. ## Re: Excel (2000)

Remember you can also turn zero displays off per worksheet. Tool, Options, View Tab, Uncheck 'Zero Values'.

Regards
Peter

11. ## Re: Excel (2000)

But, he wanted to see zero instead of nothing.

12. ## Re: Excel (2000)

Hi Legare

My reply made more sense in response to John's post..... I'll try replying to the correct post in future.

Regards
Peter

13. ## Re: Excel (2000)

It's my guess that Trisha is a she. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Personally I think that the ISERROR approach is overkill for this problem as put, but to each his or her own. If I wanted to make the formula more comprehensible in regards to handling divide by zero errors, I'd use the structure =IF(leads=0,0,calls/leads).

Trisha, are you still out there and are these answers making sense and helping?

14. ## Re: Excel (2000)

Hi John -

Yes, that information did help. I have a similar situation but want to use "Average". For example, I'd like to say: Look in cells J2:J32. If there are any errors, ignore and error and average the rest of the cells together. Will this work?

15. ## Re: Excel (2000)

This array formula (confirm with ctrl-shift-enter) should do it:

<pre>=AVERAGE(IF(ISNUMBER(J2:J32),J2:J32))</pre>

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•