Inserting 10 or more IF in one Cell.

[countryfan_nt]

Hello friends,

I have a VLOOKUP formula in cell X1. I am trying to insert all the below 10 IF statements in it. I have three problems:

A) Excel does not allow inserting that many conditions in one cell.

B) Don't want to use VBA.

C) VLOOKUP doesn't work, since the table array contains formulas. I can't have the table arrays change from 1s to 2s, when copying & pasting X1 to X2.

1. IF(A1=1,C1*D1)
2. IF(A1=2,E1*F1)
3. IF(A1=3,G1*H1)
4. IF(A1=4,I1*J1)
5. IF(A1=5,K1*L1)
6. IF(A1=6,M1*N1)
7. IF(A1=7,O1*P1)
8. IF(A1=8,Q1*R1)
9. IF(A1=9,S1*T1)
10. If all false then "NO".


All the best & Thank you so much,
Nawaf

 

[RalphA]

Norie:

Thank you! Would you be so kind as to post it? I would like to examine it, 'cause I am a very thick-headed person, and have got to see to believe!

 

[Norie]

Ralph

Oops.

Closed the file without saving it.

Like I said it wasn't very scientific, it was just something like this.

If a Then
    If b Then
         ' ... repeat ad nauseam
    End If
End If

I'll perhaps give it another go with more 'realistic' code but it's getting late here so I'm just shooting off in a bit.

 

[RalphA]

Norie:

Thank you, I was able to pick up on it and run it, using the F8 key to step it one line at a time. I used 10 Ifs, and it ran through each and every one! So, thank you for noticing my post and asking me the question, allowing me to learn something which I had thought was not so.

Once again, I have been taught that one cannot simply assume something and state it to be so, without first confirming it by experimentation. Live and learn! It seems to be an ongoing thing with me, for ever and ever! I do try to make up for things by accepting my errors. (Red faced)

 

[RalphA]

Norie, and all users of Visual BASIC, QuickBASIC, and GW-BASIC:

After I found out that VBA had no problem with at least 9 nested IF statements, I got to wondering, so, I went to each of the above BASIC-type programs, and ran similar tests. ALL OF THE TESTS RAN FINE! I have no idea where I possibly could have come up with my badly mistaken notion regarding a limit on nesting in the BASICS listed.

So, I beg forgiveness of all whom I might have misled! This fortifies my normal thinking of believeing something only after proving it to oneself!

 

[JackBean]

Thanks RalphA for repeating that tip about using string concatenations, which I had not seen. It does not seem to help though if you actually need to go through all of the levels to check conditions, etc.

There is another kind of non-VBA code workaround that may be an improvement in some cases. Using the named formula rather than an additional cell for the additional levels could save more than that one extra cell, if that cell is repeated, thus making for a smaller file.

There is another advantage to named formulas, at least with Excel 2000. There seems to be a limitation on how many cells can be filled in as array type formulas. I do not know if this involves the size of the memory chip on the computer. But with named formulas, the named formula somehow seems to know that it is an array formula, at least with the ones that I have entered, and the limitation on number of array cells is reduced or eliminated.


Here is an example using named formulas Substut and Substut1.

=SUBSTITUTE(Substut,H$2,"")

Substut=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Substut1,7,""),6,""),5,""),4,""),3,""),2,""),1,""),0,"")

Substut1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A!$D5,"[",""),".",""),9,""),8,""),"]",""),"(",""),")",""),A!$A$1,"")

This removes from cell D5 all of the characters listed. There may be a better way to do this, but it demonstrates the named formulas where all the levels are performed.

One disadvantage to named formulas is that, although columns can be inserted and deleted if the $ symnbol was used in the named formula, dragging the named cells into a different order of columns seems to cause the named cell addresses to be lost.

Named formulas are a little tricky to use. The named formulas should be entered from the cell where it will be used, and the $ symbol may need to be used for cell addresses.

Here is a reference to named formulas:

http://www.cpearson.com/excel/named.htm

AND

http://www.cpearson.com/excel/nested.htm

 

[RalphA]

HackBean:

You wrote:

Thanks RalphA for repeating that tip about using string concatenations, which I had not seen. It does not seem to help though if you actually need to go through all of the levels to check conditions, etc.

Well, and thank you! But, I don't understand what you mean by your second line, "It does not seem to help, though, if you actually need to go through all of the levels to check conditions, etc."

My point is simply that one can write a multiplicity of IF() statements, if one chooses that route, and, I like to state it and explain it, because a number of posters understand simple IF() statements and would like to use more than what they think is the magical limit of IFs in Excel formulas.

Let me add that the nested IFs are usually af the same structure, so that, one may simply write the first part, highlight it, and copy it seven more times, then add the final parenthesis and make the simple changes to each of the IFs. Then, if one wishes to concatenate, one simply adds an & symbol, highlights the first eight IFs, and copies it as many times as are necessary, thereby obtaining eight more IFs each time, after doing the necessary simple changes.

In your defense, for more advanced and high-powered programmers, more than a few IFs brings out their great skills and innovative powers, conducive to their using other, more powerful tools, be it formulas or VBA.

 

[JackBean]

RalphA:

You picked up on a point there where I misspoke. I meant to say where results are propagated through all the levels, rather than checking all the conditions, which you addressed. I see your point, and again thanks for posting that to those of us who are new to the forum.

My suggestion may help someone in a hurry until they can come up with a better formula or VBA. And I suspect that the original poster was not aware of this particular option.

There are some good solutions here which I have not had time to try out. It is certainly true that no matter what we post, someone may come along the next second and post something better. That's how we all learn. Or you write a formula, and next week improve it yourself.