Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Help editing formula to remove #VALUE error

  1. #1
    Board Regular
    Join Date
    Feb 2011
    Location
    Singapore
    Posts
    403
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help editing formula to remove #VALUE error

    Hi all -

    I have a formula which I recently had to change and now when some of the fields aren't filled it is giving me a #VALUE error. I suspect that is because in some cases it is trying to divide by nothing because a user hasn't added that value in there.

    Can someone help me integrate an additional (ISERROR) function into the formula I have below so that if there is an error it will just leave the cell blank? I just can't seem to get it right. Obviously the field it is dividing by is in column K

    =IF(ISBLANK(I4),"",SUMIFS(Rest,Date,">="&C4,Date,"<="&D4,Product,E4,Adcoop,H4,DC,I4))/K4

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,502
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Help editing formula to remove #VALUE error

    I am not going to try to make heads or tails over what your formula is trying to do, but will show you a simple example of how you can avoid the division by zero error.

    Let's say you simply want to divide two numbers (a1/b1) but if the divisor (b1) is zero/blank, you want to return nothing instead of an error. You can do that like this:

    =IF(b1=0,"",a1/b1)

    You should be able to incorporate the same sort of logic in your formula.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Feb 2011
    Location
    Singapore
    Posts
    403
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help editing formula to remove #VALUE error

    Quote Originally Posted by Joe4 View Post
    I am not going to try to make heads or tails over what your formula is trying to do, but will show you a simple example of how you can avoid the division by zero error.

    Let's say you simply want to divide two numbers (a1/b1) but if the divisor (b1) is zero/blank, you want to return nothing instead of an error. You can do that like this:

    =IF(b1=0,"",a1/b1)

    You should be able to incorporate the same sort of logic in your formula.
    Thanks Joe. I think what I am struggling with is incorporating that IF function inside my formula since it already contains another IF (in the form of the IF(ISERROR)... can you help with putting it in there?

  4. #4
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,256
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help editing formula to remove #VALUE error

    Try:
    =IF(ISBLANK(I4),"",IF(ISERROR(SUMIFS(Rest,Date,">="&C4,Date,"<="&D4,product,E4,Adcoop,H4,DC,I4)/K4),"",SUMIFS(Rest,Date,">="&C4,Date,"<="&D4,product,E4,Adcoop,H4,DC,I4)/K4))
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  5. #5
    Board Regular
    Join Date
    Feb 2011
    Location
    Singapore
    Posts
    403
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help editing formula to remove #VALUE error

    Quote Originally Posted by Robert Mika View Post
    Try:
    =IF(ISBLANK(I4),"",IF(ISERROR(SUMIFS(Rest,Date,">="&C4,Date,"<="&D4,product,E4,Adcoop,H4,DC,I4)/K4),"",SUMIFS(Rest,Date,">="&C4,Date,"<="&D4,product,E4,Adcoop,H4,DC,I4)/K4))
    Boom goes the dynamite! Thanks Buddy!

  6. #6
    New Member
    Join Date
    Mar 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help editing formula to remove #VALUE error

    I have a formula which when no data is input in the row gives me a #VALUE error.

    Can anyone help me integrate an additional (ISERROR) function into the formula I have below so that if there is an error it will just leave the cell blank? I just can't seem to get it right. the cell is blank when there is nothing to lookup in that row to our master sheet with all the content info.

    =IF(B11="","",VLOOKUP(B11,'K:\Excel\[Catnic Master File.XLS]WS'!$B$15:$R$5000,6,FALSE))*C11

    thanks so much for reading.

    Kind regards,
    Grant

  7. #7
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,502
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Help editing formula to remove #VALUE error

    Grant,
    I see you also asked the question over here and got it answered: http://www.mrexcel.com/forum/showthread.php?t=624752

    Please do not post the same question multiple times. Per forum rule #9 here, posts of a duplicate nature will typically be locked or deleted: http://www.mrexcel.com/forum/showthread.php?t=99490

    Thanks
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

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
  •