Results 1 to 15 of 15

Thread: Excel (2000)

  1. #1
    Star Lounger
    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.

  2. #2
    Uranium Lounger
    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

  3. #3
    Star Lounger
    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

  4. #4
    Uranium Lounger
    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

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, 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
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel (2000)

    Crossed in the ether!

    Regards
    Peter

  7. #7
    Star Lounger
    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!

  8. #8
    Uranium Lounger
    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

  9. #9
    Uranium Lounger
    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

  10. #10
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, 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

  11. #11
    Uranium Lounger
    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

  12. #12
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, 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

  13. #13
    Uranium Lounger
    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

  14. #14
    Star Lounger
    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?

  15. #15
    WS Lounge VIP sdckapr's Avatar
    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 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
  •