Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If Statements (Excel 97)

    Need your help. I have attached a screen shot for illustration purposes. Here's what I am trying to do - If H9=no and I9=no, then get value at U51. If H9=yes and I9=yes, then get value at u41. If H9=yes and I9=no, then get value at u49. If H9=no and I9=yes, then get value at u45. It gives me a "false" answer while it ought to be the value at u51. I thank all of you in advance for your response

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

    Re: If Statements (Excel 97)

    See if the formula below works better:

    <pre>=IF(AND(H9="NO",I9="YES"),'[BAS-GMV.xls]Sheet1'!$U$45,IF(AND(H9="YES",I9="NO"),'[BAS-GMV.xls]Sheet1'!$U$49,IF(AND(H9="YES",I9="YES"),
    '[BAS-GMV.xls]Sheet1'!$U$41,IF(AND(H9="NO",I9="NO"),'[BAS-GMV.xls]Sheet1'!$U$51,"ERROR"))))
    </pre>


    The above goes all on one line of course.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: If Statements (Excel 97)

    The only caveat that I might add is that since you are using Excel 97, you might be running in to the size limitation for the cell if your formula gets too complicated. If you are also running Win 95, built as a 16 bit system, then your cell limitation in Excel is 255 characters. The solution that Legare shows has 223 characters. So if your path names are longer than his, you have exceeded the cell limitation.

    Dennis

    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>

  4. #4
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Statements (Excel 97)

    I thank both of you for responding. Mr. Coleman - I employed your suggestion by replacing the " " with "error"; however, it still gives a false response. Mr. Dennis - I a not sure if this would make a difference but I have Excel 97 in an NT environment while the if statement has four nested arguments. At any rate, at the risk of sounding confused while joyfully perturbed, would there be a proposed work-around? Thanks - GCB

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

    Re: If Statements (Excel 97)

    Try this:

    =CHOOSE(1+(H9="YES")+2*(I9="YES"),'BAS-GMV'!U51,'BAS-GMV'!U49,'BAS-GMV'!U45,'BAS-GMV'!U41)

    Explanation: in formulas, TRUE = 1 and FALSE = 0. So 1+(H9="YES")+2*(I9="YES") evaluates as follows:

    <table border=1><td>H9</td><td>I9</td><td>1+(H9="YES")+2*(I9="YES")</td><td>NO</td><td>NO</td><td align=right>1</td><td>YES</td><td>NO</td><td align=right>2</td><td>NO</td><td>YES</td><td align=right>3</td><td>YES</td><td>YES</td><td align=right>4</td></table>
    The CHOOSE function looks at the value n of first argument and returns the n-th of the following arguments.

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

    Re: If Statements (Excel 97)

    I did a lot more than replace the " " with "ERROR". The parenthesis in your formula are not in the correct place. You must replace your entire formula with my formula. I tested my formula, and it does work correctly. Copy it from my message and paste it into your workbook and it should work,
    Legare Coleman

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

    Re: If Statements (Excel 97)

    Hi Legare,

    Your formula won't work "as is" - the screenshot attached to the first post in this thread shows references to a worksheet BAS-GMV within the same workbook. Your refer to Sheet1 in a workbook BAS-GMV.xls. Apart from that, it works (of course, no doubt about that.)

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

    Re: If Statements (Excel 97)

    Thanks, I did misread the screen shot.
    Legare Coleman

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

    Re: If Statements (Excel 97)

    Hans just pointed out that I misread your screen shot to think that BAS-GMV was a different workbook, not another sheet in the same workbook. Here is the corrected formula:

    <pre>=IF(AND(H9="NO",I9="YES"),'BAS-GMV'!$U$45,IF(AND(H9="YES",I9="NO"),'BAS-GMV'!$U$49,IF(AND(H9="YES",I9="YES"),
    'BAS-GMV'!$U$41,IF(AND(H9="NO",I9="NO"),'BAS-GMV'!$U$51,"ERROR"))))
    </pre>

    Legare Coleman

  10. #10
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Statements (Excel 97)

    To all respondents - thanks for the helping hand. Legare your formula worked since I neglected to add the closing parenthesis. I disregarded the workbook sheet1 naming in your formula since the screen shot and my emaiI failed to explain that it is in a worksheet within the workbook. Hans thanks for showing me the choose function. I will play around with that since I need a garden variety of reference items. GCB

  11. #11
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Statements (Excel 97)

    Hans - Need you help. Since the choose function is new to me, would you kindly explain this part of the argument CHOOSE(1+(H9="YES")+2*(I9="YES")? I mean, why is there a "1+(H9 . . .)+2*( . . .)"? Thanks

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

    Re: If Statements (Excel 97)

    The worksheet function CHOOSE works as follows:

    =CHOOSE(n,value1,value2,value3)

    If the first argument n = 1, value1 is returned; if n = 2, value2 is returned, and if n = 3, value3 is returned. There can be up to 29 arguments after n.

    You wanted to select a cell based on the values of H9 and I9, either of which can be either "YES" or "NO". I wanted to avoid nested IF's. I used the fact that Excel treats TRUE as equivalent to 1 and FALSE equivalent to 0. So (H9="YES") is 1 if H9 equals "YES" and (H9="YES") is 0 otherwise (if H9 equals "NO").

    Next, I used a trick based on binary numbers: how can I create distinct numbers from a series of numbers that can be either 0 or 1? By multiplying the first number by by 1, the second by 2, the third by 4, the fourth by 8, etc. (each time, double the multiplier) and adding the results. Here is an example that shows how to create a unique number out of each distinct combination of three 0'1 and 1's:

    <table border=1><td>a</td><td>b</td><td>c</td><td>1*a+2*b+4*c</td><td>1+1*a+2*b+4*c</td><td align=right>0</td><td align=right>0</td><td align=right>0</td><td align=right>0</td><td align=right>1</td><td align=right>1</td><td align=right>0</td><td align=right>0</td><td align=right>1</td><td align=right>2</td><td align=right>0</td><td align=right>1</td><td align=right>0</td><td align=right>2</td><td align=right>3</td><td align=right>1</td><td align=right>1</td><td align=right>0</td><td align=right>3</td><td align=right>4</td><td align=right>0</td><td align=right>0</td><td align=right>1</td><td align=right>4</td><td align=right>5</td><td align=right>1</td><td align=right>0</td><td align=right>1</td><td align=right>5</td><td align=right>6</td><td align=right>0</td><td align=right>1</td><td align=right>1</td><td align=right>6</td><td align=right>7</td><td align=right>1</td><td align=right>1</td><td align=right>1</td><td align=right>7</td><td align=right>8</td></table>
    By adding 1 to the result of 1*a+2*b+4*c, we get values 1, 2, ..., 8. These can be used in the CHOOSE function.

    Are you still there?

    In the formula for you, I used this with 2 values, and substituted (H9="YES") for a, and (I9="YES") for b.

  13. #13
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Statements (Excel 97)

    Hans - I'm still here. I copied your formula and it works! I think that I know what you are trying to do but it just did not sink in although I see potential with this approach. I mean, an if statement could, I think, go for seven nestings. If it would not be too much to ask of you, is there a way that you could explain this to me step-by-step? This is where I am coming from . . . If H9=yes and I9=yes, then what is that distinct number that would return n-value from the list? Once again, I thank you for your patience. GCB

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

    Re: If Statements (Excel 97)

    I don't think I can explain it more clearly than I already did in my previous replies in this thread <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    I you want to apply this with more than two cells, nested IFs will only allow to test three YES/NO cells, since there are 2*2*2 = 8 different combinations of 3 YES/NO's. You can exactly get this with 7 nested IFs.
    My method will allow you to test four YES/NO cells, since there are 2*2*2*2 = 16 different combinations; five would be too much: 2*2*2*2*2 = 32 combinations, but CHOOSE only allows up to 29 choices.
    If you want to test more cells, you would need to put the values you want to select in a contiguous range; then, it is possible to use the INDEX or OFFSET function to select the appropriate value. There is no limit on the number of YES/NO cells.

Posting Permissions

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