Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Two Rivers, Wisconsin, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If Function Trouble (Office 97)

    I use an IF function to display a constant depending on a value in another cell being ">0". That value is derived from another IF statement in another (second) cell. The first cell always displays a value, even if there is no value in the second cell. If I delete the IF statement in the second cell, the first cell then displays the correct results. Why is the IF statement in the second cell interpreted as "a value greater than zero"?

  2. #2
    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: If Function Trouble (Office 97)

    What are the values in the cells and what are the IFs.
    Is the second cell text - (null string ("") is text)?
    If you compare different variable types, text always > a number (it compares both as text),
    a blank cell = 0 (so is not ">0")

    Steve

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Two Rivers, Wisconsin, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Function Trouble (Office 97)

    The first cell is a constant; happens to be "miles". The second cell is "dollars" entered as a result of the IF statement in that cell. I just changed the first cell IF statement to call not for the constant but rather just the value displayed in the second cell. That resulted in a correct response in that there was no value displayed where there was no value in the second cell, and a ($)value displayed where there was a value in the second cell. But I don't want the dollar value, I just want a fixed number specified in the IF statement in the first cell.

  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: If Function Trouble (Office 97)

    I'm sorry but I don't follow your explanation at all. Could you post the formulas and what values you expect?

    Steve

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

    Re: If Function Trouble (Office 97)

    Could you upload a sample workbook showing the problem. I can follow your explanation well enough to understand what the problem is.
    Legare Coleman

  6. #6
    New Lounger
    Join Date
    Feb 2004
    Location
    Two Rivers, Wisconsin, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Function Trouble (Office 97)

    Steve
    I will attach a sample of what I am doing. The number "6" in the "RX Miles" I want to appear only when a "$" value is shown in a cell in the "Drug" column.
    Thanks
    John

  7. #7
    New Lounger
    Join Date
    Feb 2004
    Location
    Two Rivers, Wisconsin, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Function Trouble (Office 97)

    Legare
    Can you pick up the sample of my problem from my response to skckapr (Steve)?
    Appreciate your help.
    John

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: If Function Trouble (Office 97)

    your sample spreadsheet is password protected - we can not look at it.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    New Lounger
    Join Date
    Feb 2004
    Location
    Two Rivers, Wisconsin, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Function Trouble (Office 97)

    Sorry about that. Here it is again.

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

    Re: If Function Trouble (Office 97)

    Change the formula in column I to:

    <pre>=IF(G3<>" ",6,"")
    </pre>

    Legare Coleman

  11. #11
    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: If Function Trouble (Office 97)

    I anddition to Legare's suggestion, another suggestion to eliminate the need to have a separate formula in each column:
    Insert a new row 2 (row can be hidden)
    In D2 - G2 enter the values for the comparison in the column (d, e, i, g, respectively)

    Then in D4 enter:
    =IF($B4=D$2,$C4," ")
    This formula may then be copied from D4:G11.

    Steve

  12. #12
    New Lounger
    Join Date
    Feb 2004
    Location
    Two Rivers, Wisconsin, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Function Trouble (Office 97)

    It works. Thank you all. One question though. What is the difference between quotation marks with and without a space between them as Legare's suggestion contains?
    John

  13. #13
    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: If Function Trouble (Office 97)

    The length of a string with no space = 0, the length of the string with 1 space = 1. When you compare "" to " " they are not equal since they have different lengths. The "null string" ("") is a string that has no characters so is not the same as a string composed of a space (" ").

    Steve

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

    Re: If Function Trouble (Office 97)

    I put the space between the quotes because your formula in column G has a space between the quotes. Your formula in column put a string with one space into the cell in G. That is not the same and not equal to a null string (""). If you want to check for any null or any number of blanks, then you could use:

    <pre>=IF(TRIM(G3)<>"",6,"")
    </pre>

    Legare Coleman

Posting Permissions

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