Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Behavior change (2002 SP3)

    Using Excel 2002 (10.6501.6735) SP3:

    I have a complex spreadsheet on which I don't want a lot of zeros displayed if there isn't a meaningful number. At the same time I want an indication that there is a formula in the cell. When I originally built the spreadsheet the technique I used was as follows:

    A1: =IF(B1>0,A2*B1,"-")
    A2: 1
    A3: A1+A2

    If B1 is zero then A1 would show "-" and A3 would show "1". However, now if B1 is zero then A1 still shows "-" but A3 shows "#VALUE!".

    I believe that this is a "feature" of SP3 because if I put these formulas on an old worksheet, it still works as before. The error only occurs on new worksheets.

    Can anyone confirm that this is SP3 behavior and if so, is there some way to recover the old functionality?

    TIA
    Donald

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

    Re: Behavior change (2002 SP3)

    Trying to add a text value and a number using + has always resulted in an error, in all versions of Excel. I don't understand how it could have worked without an error in your older worksheets.
    If you use SUM instead of +, text values will be ignored, so you could use =SUM(A1:A2) instead of =A1+A2.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Behavior change (2002 SP3)

    Most likely the dash was being treated as minus zero and is now being treated as a text value which causes the error.

    Try changing A3 to:

    =SUM(A1,A2)
    Legare Coleman

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Behavior change (2002 SP3)

    Another option is to let it be zero and format the cells (via format - cells) to display the dash (or whatever you want). The advantage of the custom format is that cell has the value of zero so calculations will still work with it, but it will be "displayed" as something else.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Behavior change (2002 SP3)

    You could always conditionally format the cell to be white ink on a white background if the value is zero.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Behavior change (2002 SP3)

    If you wanted to hide them completely (white on white) then you could use Tools - options- view (tab) and uncheck "zero values". This will even hide them if the background is not white and you are printing.

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Behavior change (2002 SP3)

    Thanks for all the replies.
    Hans: I've got living, breathing proof that it works - and has worked for years. Must have been something I got away with by luck all this time.

    My example is a vastly simplified example of the behavior. Due to the nature of the actual spreadsheet I'm not sure that using SUM would be practical, but will give it a try. Since I'm having to do a lot of work on the sheet anyway, I may go with the custom formatting option.

    Thanks again.
    Donald

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Behavior change (2002 SP3)

    One warning about custom formatting vs an IF with a text value:

    If you have a 1 and a text "-" it will average to be 1 (the text is not a number and is ignored). If you use the custom format to display the zero as a "-", you will have a 1 and a 0 so the average will be 0.5. Even though it displays as a "-" it has a value of 0. it is a number so it will not be ignored in averages.

    If you are just using SUMs it will not matter (zero or ignored are the same sum) it will make a difference in other stats.

    Steve

Posting Permissions

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