Results 1 to 7 of 7

Thread: #Value Error

  1. #1
    New Member
    Join Date
    Aug 2019
    Location
    United Arab Emirates
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default #Value Error

    I create statement sheet, which have Debit (H), Credit (F) and Balance (G) Column.
    Both Debit (H) and Credit (F) have Formulas to to calculate them Values.
    In Balance I make this Formula, =IF(ISBLANK(C8),"",H7+F8-G8)
    I use IF(ISBLANK just to keep the cell blank if there are no Data in that raw.
    The problem is that I am getting #Value in Balance because either the (H) or the (F) Column have no Value.
    How I can fix this??
    Thanks

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: #Value Error

    I don't think it like setting numeric fields equal to "".
    What if you try:
    Code:
    =IF(ISBLANK(C8),0,H7+F8-G8)
    If that does not work, please tell us the contents of C8, H7, F8 and G8.
    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 DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,976
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: #Value Error

    Both Debit (H) and Credit (F) have Formulas to to calculate them Values.
    If the result of these formulas is "" (Blank), you should change it to 0 (zero), so you would not have problems.
    But if you want to leave the result as "" (Blank), then try:

    =IF(ISBLANK(C8),0,H7+IF(F8="",0,F8)-IF(G8="",0,G8))
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,487
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: #Value Error

    Quote Originally Posted by hannakaram View Post
    =IF(ISBLANK(C8),"",H7+F8-G8)
    [....]
    The problem is that I am getting #Value in Balance because either the (H) or the (F) Column have no Value.
    No. If H7 and F8 truly have "no value" (empty), they are interpreted as zero.

    They might appear to be empty because their value (or G8) is the null string ("").

    Moreover, your test ISBLANK(C8) might not always do what you intended if C8 might be the null string (""), not empty (no value).

    To correct all of that, try:

    =IF(C8="", "", SUM(H7,F8)-N(G8))

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,976
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: #Value Error

    Or also

    =IF(C8="","",SUM(H7,F8,-N(G8)))
    Regards Dante Amor

  6. #6
    New Member
    Join Date
    Aug 2019
    Location
    United Arab Emirates
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #Value Error

    Quote Originally Posted by DanteAmor View Post
    Or also

    =IF(C8="","",SUM(H7,F8,-N(G8)))

    Yesssss
    This is the right one

    Thanks a lot

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,976
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: #Value Error

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

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
  •