#DIV/0!
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: #DIV/0!

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Christy
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello!
    I have a worksheet that makes calculations in cells based on the outcome of a calculation or input in another cell. If an input cell is empty it throws off the whole calculation cycle.
    Here's a little more information:
    Cells A5:D5 receive operator input of a quantity for items, if each item is ordered.
    Cells A7:D7 divide the number in Cells A5:D5 by a number in A6:D6
    Then those numbers in cells A7:D7 are added up in Cell G5.
    If there is no amount put in any one or more of the cells A5:D5 (because no one ordered that item), it throws the whole calculation off and all you get is “#DIV/0!”
    Looking through HELP, I believe it has something to do with “ISERROR” but, unfortunately, I am too ignorant to understand how to use it.
    Here are the actual formulas I have for 1 column:
    A5 = operator input (# of items ordered)
    A7 = =ROUNDUP(A5/A6,0)
    A46 = =ROUND((PRODUCT(A7,0.65)),1)
    G5 = =ROUND(((PRODUCT(A6,A46)+PRODUCT(B6,B46)+PRODUCT(C6,C46)+PRODUCT(D6,D46))/1.72),1)
    If A5 is blank, everything else reads "#DIV/0!"
    Thank you so much for any help!
    ~Christy

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here are two tips:

    first, you can simplify some of your formulas. PRODUCT is great for multiplying lots of numbers in a range but * is often much easier.
    ROUND((PRODUCT(A7,0.65)),1)
    can just be
    ROUND(A7*0.65,1)

    ROUND(((PRODUCT(A6,A46)+PRODUCT(B6,B46)+PRODUCT(C6,C46)+PRODUCT(D6,D46))/1.72),1)
    can just be
    ROUND(((A6*A46)+(B6*B46)+(C6*C46)+(D6*D46))/1.72),1)
    or even
    ROUND(SUMPRODUCT(A6:D6,A46:D46)/1.72,1)

    second, about the #DIV/0!.
    It not clear why A5 would generate the error,
    but if A6 is zero or blank, A7 winds up with an error because it divides by A6.

    There are a lot of ways to handle this. One way is for A7 to be =IF(A6=0,0,A5/A6).

    Hope this helps!
    Sincerely,
    Tim F-W
    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Charlotte, NC USA
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'll take a swag @ it - try this:

    A7=if(iserror(ROUNDUP(A5/A6,0)),"n/m",ROUNDUP(A5/A6,0))

    G5=if(iserror(ROUND(((PRODUCT(A6,A46)+PRODUCT(B6,B46)+PRODUCT(C6,C46)+PRODUCT(D6,D46))/1.72),1)),"nm",ROUND(((PRODUCT(A6,A46)+PRODUCT(B6,B46)+PRODUCT(C6,C46)+PRODUCT(D6,D46))/1.72),1))

    you can use 0 instead of "nm"
    Thxs for tips - this place is a great source for tips & tricks

    Gary

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    like tim i can't see why you'd be getting the #DIV/0! result because all your denominators are >0, but using the auditing toolbar may help. right click on a grey bit of the menu bar, select 'customise' at the bottom of the menu and on the toolbars tab check the auditing box. this toolbar allows you to trace precedent and dependent cells to formulas, as well as tracing errors, which is the button with the yellow diamond and the "!". handy for big sheets but i also find it a life saver when i have looked at something so many times i am looking straight past the error.

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Christy
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thank you so much! That was a very simple solution, Tim - you can't imagine how wonderful it was to find I only had to change ONE cell & all the rest worked right! Thank you ALL - I love all the tips!! ~Christy

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
  •  

 

 
DMCA.com