Results 1 to 15 of 15
Thread: Excel (2000)

20021118, 20:15 #1
 Join Date
 Jun 2002
 Location
 Worthington, Ohio, USA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
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
2 lead
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.
Thanks for your help.

20021118, 20:23 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Excel (2000)
Because in Excel false=zero, the simplest way to handle this is:
=IF(leads,calls/leads,)John ... I float in liquid gardens
UTC 7ąDS

20021118, 20:26 #3
 Join Date
 Jun 2002
 Location
 Worthington, Ohio, USA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel (2000)
This still gave me the same error

20021118, 20:32 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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,)John ... I float in liquid gardens
UTC 7ąDS

20021118, 20:33 #5
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel (2000)
Johns advice is correct, the attached spreadsheet has a worked example.
Regards
Peter

20021118, 20:34 #6
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel (2000)
Crossed in the ether!
Regards
Peter

20021118, 21:06 #7
 Join Date
 Jun 2002
 Location
 Worthington, Ohio, USA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!

20021118, 21:15 #8
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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
=IF(leads,calls/leads,"")
which may cause problems if other formulas depend on it. Does this help?John ... I float in liquid gardens
UTC 7ąDS

20021119, 00:33 #9
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.Legare Coleman

20021119, 07:08 #10
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel (2000)
Remember you can also turn zero displays off per worksheet. Tool, Options, View Tab, Uncheck 'Zero Values'.
Regards
Peter

20021119, 14:01 #11
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel (2000)
But, he wanted to see zero instead of nothing.
Legare Coleman

20021119, 14:09 #12
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20021119, 15:27 #13
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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?John ... I float in liquid gardens
UTC 7ąDS

20030623, 17:53 #14
 Join Date
 Jun 2002
 Location
 Worthington, Ohio, USA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20030623, 18:39 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel (2000)
This array formula (confirm with ctrlshiftenter) should do it:
<pre>=AVERAGE(IF(ISNUMBER(J2:J32),J2:J32))</pre>
Steve