Results 1 to 14 of 14
Thread: If Statements (Excel 97)

20030408, 01:39 #1
 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

20030408, 02:51 #2
 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"),'[BASGMV.xls]Sheet1'!$U$45,IF(AND(H9="YES",I9="NO"),'[BASGMV.xls]Sheet1'!$U$49,IF(AND(H9="YES",I9="YES"),
'[BASGMV.xls]Sheet1'!$U$41,IF(AND(H9="NO",I9="NO"),'[BASGMV.xls]Sheet1'!$U$51,"ERROR"))))
</pre>
The above goes all on one line of course.Legare Coleman

20030408, 12:49 #3
 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>

20030408, 19:54 #4
 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 workaround? Thanks  GCB

20030408, 20:20 #5
 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"),'BASGMV'!U51,'BASGMV'!U49,'BASGMV'!U45,'BASGMV'!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 nth of the following arguments.

20030408, 22:05 #6
 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

20030408, 22:22 #7
 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 BASGMV within the same workbook. Your refer to Sheet1 in a workbook BASGMV.xls. Apart from that, it works (of course, no doubt about that.)

20030408, 22:44 #8
 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

20030408, 22:47 #9
 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 BASGMV was a different workbook, not another sheet in the same workbook. Here is the corrected formula:
<pre>=IF(AND(H9="NO",I9="YES"),'BASGMV'!$U$45,IF(AND(H9="YES",I9="NO"),'BASGMV'!$U$49,IF(AND(H9="YES",I9="YES"),
'BASGMV'!$U$41,IF(AND(H9="NO",I9="NO"),'BASGMV'!$U$51,"ERROR"))))
</pre>
Legare Coleman

20030408, 22:52 #10
 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

20030409, 18:51 #11
 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

20030409, 19:08 #12
 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.

20030411, 00:46 #13
 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 stepbystep? This is where I am coming from . . . If H9=yes and I9=yes, then what is that distinct number that would return nvalue from the list? Once again, I thank you for your patience. GCB

20030411, 06:55 #14
 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.