Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Value Error (Excel XP)

    A apologize if the attached information is not 100% clear. Unfortunately I cannot attach a cut down versionof the spreadsheet due to set up of it. Text boxes, check boxes, graphics etc.

    Basically as can be seen in the attached file, there are two formulas that should do the same thing. One I created in another worksheet, and it works as it should. The second formula returns a Value error as explained. I don't understand why the second one will not work as I assume it should.

    Again my apology for the way the information is presented. Can someone possible take a look and lead me in the right direction?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Value Error (Excel XP)

    If either E69 or E94 contains an empty string "" as result of a formula, the formula in E96 will return an error because you cannot subtract and multiply strings. So you should use 0 instead of "" in the formulas. If you wish, you can hide the zeros either by clearing "Zero values" in the View tab of Tools | Options or by using a custom number format.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value Error (Excel XP)

    Thanks, Hans.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value Error (Excel XP)

    I hesitate to add anything to Hans' wisdom, but
    >>If you wish, you can hide the zeros either by clearing "Zero values" in the View tab of Tools | Options or by using a custom number format.
    The danger of the Tools | options solution is that I think it's global for all of excel until you reset it.
    Anothe option is to use Conditional Format to write the zeros in whte ink on a white background. Or whatever turns you on and has the effect of invisible (in the cell, but not in the formula bar, which will show your formula.)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Value Error (Excel XP)

    The "Zero values" setting applies to (the current window on) the current sheet, clearing it does not affect other worksheets or other workbooks. But it does apply to ALL zero values on the current sheet, including manually entered zeros. A custom number format would be my preferred solution: a format such as <code>0;0;</code> will hide all zeros within the range you apply the formatting to.

Posting Permissions

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