Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: #VALUE! error causing calculation problems in SUM formula

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Oregon
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay. I've heard people say that you should not get a #VALUE! error simply because of blank cells - that a blank cell is interpreted as zero. Well, that does not seem to be the case for me. I have a spreadsheet that is summing data in column W for all the previous cells (Ex: A3:V3) in that row. Row 1 will be fine, row 2 will be fine, but row 3 doesn't have any data, so cell W3 displays the error message "#VALUE!"

    Now, in cell W26, I'm summing W1:W25. I have 3 instances in column W where the formula result displays as #VALUE!. Therefore, W26 is also displaying as #VALUE!.

    So, in very literal terms (because I'm much more of an English person than a computer guru), could someone explain to me how to fix this?

    Thanks in advance to all the smarties!

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

    Default

    Make sure that there are no hidden columns with errors that you're including in you sum formula.

    Try deleting the sum formulas and use Excel to find the errors by: Choosing Edit > Goto from the menu, click Special, click Formulas and uncheck all but errors, click OK. Excel should select all cells with errors, or alert you if none is found.


    It's never too late to learn something new.

    Ricky

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Care to post the formula that you must have in W1:W25, the range that you want to sum/total in W26?

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Oregon
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay, here's what happened. I looked at every cell being pulled in for the formula, and there was no data in any of them. HOWEVER, I thought, just for the heck of it, I'd try clearing those cells, and that worked. What the heck? I sincerely do not understand the workings of the world sometimes.

    But, just out of curiosity, IS there a way to format a column so that if a formula results in an error message, a zero is returned instead? I know maybe it's better to find the error, but in cases like the question I asked, where it was something ridiculous like it was, and it would involve me clearing the offending cells (and the spreadsheet might be thousands of rows long), how would one go about trading errors for zeroes? If there is, in fact, a way.

    Thanks for your help so far, people!

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try using the on-line help and research the ISERROR function. Can set as part of a formula:

    =if(iserror(A1),"0",(your formula))

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-15 14:03, nancyo wrote:
    Try using the on-line help and research the ISERROR function. Can set as part of a formula:

    =if(iserror(A1),"0",(your formula))
    No need for double quotes around the zero.

    Aladin

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
  •