Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: #DIV/0 how can I avoid this?

  1. #1
    New Member
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In A1 to A5, I have blank cells that I will eventually insert values into. A6 contains the total of A1 to A5 example:=sum(A1:A5)

    In B1 to B5, I want to divide the number in column A by
    the total (A6)
    formula is: =A1/A6 and so on.

    The problem is that if I don't have a value in A1 to A5 yet, the the formula cells in column B show #DIV/0!
    What can I put in that formula to make the B cells show nothing until there is a value in the A cells.

    I have a small checkbook worksheet I got off that net that has something simular where nothing shows in the balance column until there is a date in the first column. It looks like this:
    =IF(B35="","",(G34-E35+F35))
    The double quotes and comma's have something to do with 'if there is not a date in the date column, don't show anything in the balance column. In other words, there is no entry there yet.

    I know this is an awfully long explanation. I'm sure there is a fancy Excel word I am looking for, but that's the problem, I don't know what that's called. Can someone tell me what I need in that formula and what Excel calls it when you don't want anything to show in the formula column until there
    is data entered that relates to the formula.

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    use this for cell b2
    then fill it down
    =IF(A1=0,0,(A1/$A$6))



  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try wrapping your formula in this:

    =IF(ISERROR(your formula),"",your formula)

    The quotes can be substituted with a 0 if you like.

    Waxaholic

  4. #4
    New Member
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That worked, thanks. barryt, why do you use 2 zero's? Is there a time you would use 1 or 3 zero's? What is the difference between using zero's and double quotes? I'm just curious as I have to do this often with varying things in the cell. Dates, dollar amounts, plain numbers, etc.

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 19:34, Phylis Sophical wrote:
    That worked, thanks. barryt, why do you use 2 zero's? Is there a time you would use 1 or 3 zero's? What is the difference between using zero's and double quotes? I'm just curious as I have to do this often with varying things in the cell. Dates, dollar amounts, plain numbers, etc.
    Hi,

    Double quotes is when the cell is empty.

    Lighting

  6. #6
    New Member
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    And what's the difference with double zero's? Why would you use one verses the other?

  7. #7
    New Member
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well I got smart and looked in Help for the error message. Apparently the double quotes denotes a text string which explains it's use in the date column of my checkbook. It would follow then that double zero's denote number strings.

    On the other hand, Wax, your formula with the quote marks worked too. Thanks from the bottom line of my spreadsheet!

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =IF (A1=0,0,(A1/$A$6))

    I used a1=0 instead of a1="" because if the cell has a value of 0 instead of a blank cell you would still get the error message

    If a1 =0 then it makes the cell = 0 or i you could have it make it blank

    When a1 doesn't = 0 then it put the a1/$a$6 formula in and it able to calculate it

    Another way that you could do the formula instead of the =IF (A1=0,0,(A1/$A$6))
    Is
    If($a$6 =0,0,(a1/$a$6))
    This is what you would put in the cell b1
    Then you could fill it down to b5

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Frequently, I see IFs such as...

    =IF(B1=0,0,A1/B1)

    This formula has an unnecessary relational operator (bolded above) which requires additional processor time.

    =IF(B1,A1/B1,0) does exactly the same thing without this comparison because Excel's logical functions treat all non-zero numeric values as TRUE and 0 as FALSE.

  10. #10
    New Member
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Marc the true / false values (0=false) alone increase my learning curve by 50%. Understanding the logic of Excel is half the battle.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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