How to Use More Than 7 IF Functions?

[jerryj]

Does anyone know a easy way to get around the 7 IF Function Limit. All help will be greatly appreciated.

 

[thereuare]

That's why i called it a work around... it works, although it's not the most efficient way to do it.

For a newbie that doesn't know/understand VLOOKUP and wants to understand what their code is doing, i thought my example just showed another way to accomplish the task, especially if the file is not labor intensive OR speed is not an issue.

I did not imply that my approach was better than anyone elses, it's just another way to accomplish what needs to be done (and simpler b/c i think most newbies are familiar with IF statements, but not always familiar with VLOOKUP and its syntax).

 

[jerryj]

Don't understand VLOOKUP!! Can you explain!!

 

[PaddyD]

what don't you understand? If the help file isn't enough, see if the excel pages on:

http://www.thewordexpert.com

help. there's an explanatory bit there for "the dreaded vlookup".

Post back with details about what bits you're having problems with if you need.

Paddy

 

[elgringo56]

Jerry, I to did not understand Vlookup, Help is kinda nasty on that one. If you are knowledgabel in VBA as the last poster is, it suppose it seems simple. The split cell If's are good for a beginner, but, study the VLOOKUP, it works great once you do finally understand it. Dont be intimidated by super knowledgabel experts that have no patience with us beginners. VBA is fun, but it takes time to learn on your own. Have fun

 

[Ivan F Moala]

On 2002-08-31 23:23, elgringo56 wrote:
{Snip} If you are knowledgabel in VBA as the last poster is, it suppose it seems simple. ........ Dont be intimidated by super knowledgabel experts that have no patience with us beginners.

elgringo, this is the 2nd post you have alluded to Paddy....he is hardly intimidating
the OP with this ?? or am I reading this wrong......?!

what don't you understand? If the help file isn't enough, see if the excel pages on:

http://www.thewordexpert.com

help. there's an explanatory bit there for "the dreaded vlookup".

Post back with details about what bits you're having problems with if you need.

Paddy

Please let the OP speak for himself.

 

[Tommy Bak]

Hi
Allthough the vlookup-idea is the best idea, it's posible to use more than 7 if's in one cell.
If you chain them instead of embedding them you would get the result you're looking for.
Try this and see if it works for you. (it does for me)

=IF(OR(V1=V40,V1<=V41),"Philly Pride II","")&IF(AND(V1>V41,V1<=V42),"Egoholics","")&IF(AND(V1>V42,V1<=V43),"Hail Marys","")&IF(AND(V1>V43,V1<=V44),"Rice A Roni","")&IF(AND(V1>V44,V1<=V45),"Dallas Devestators","")&IF(AND(V1>V45,V1<=V46),"Packers2003","")&IF(AND(V1>V46,V1<=V47),"Clutch Ballers","")&IF(AND(V1>V47,V1<=V48),"Hamilton Hurricanes","")&IF(AND(V1>V48,V1<=V49),"Womping Wombats","")&IF(AND(V1>V49,V1<=V50),"Under Dogs","")

regard Tommy

 

[XL-Dennis]

Tommy, et al,

Sorry for stepping in but I couldn´t resist :wink:

The presented formula works however I find this formula to be very difficult to handle:

* Upon any changes, i e maintain, it take some time before we understand where to change it (at least for me....).

* How to explain it to other user when the questions are raised what's this and how does it work (here we need a picture of a decision-tree!)

* Since we have some cells to work with, i e 65536 x 256 and the ability to hide columns/rows, we are not forced to "compress" formulas and tables.

* Finally, I´m always trying to avoid hardcoding values, instead using names and cells.

Oh well, that´s my 2cents-opion

Kind regards,
Dennis

Edit: My poor english...
_________________
"Windows was not able to find any keyboard. Press F1-button to try again or F2-button for cancel."
This message was edited by XL-Dennis on 2002-09-01 05:12

 

[thereuare]

I appear to have created a controvery w/ my 3 columns of IF statements, which i did not intend on doing, and i apologize for that, i hope the experts on this board that have given me GREAT advice in the past continue to do so.

Nowhere did i say that VLOOKUP is painful, and i do have spreadsheets where i use them myself, but when i first started to use VLOOKUP it took quite a bit of troubleshooting on my part to get the desired data i was seeking. VLOOKUP is not insurmountable by any means, but it definately is more complex than IF statements, especially for a beginner.

As i know i had trouble using VLOOKUP at the beginning, i was merely offering an alternative that the OP might have begun to use immediately, if s/he too, didn't think VLOOKUP was intuitive.

 

[XL-Dennis]

thereuare,

I appear to have created a controvery w/ my 3 columns of IF statements, which i did not intend on doing, and i apologize for that, i hope the experts on this board that have given me GREAT advice in the past continue to do so

I understand what You mean but please continue to give the board Your solutions too

I honest believe that this board is also friendly in the terms that we are all welcome to contribute, either with questions or with answers or with both

In order to develop the knowledge and to explore XL it would be a "distaster" if only there was some members of this board that supplied all the answers.

To freely quote Mr Voltaire:
"If everyone is agreed with everyone there wouldn´t be any discussions and no progress".

 

[Tommy Bak]

Hi Dennis
I do agree with you on most point here, but the question was :
Does anyone know a easy way to get around the 7 IF Function Limit?

As JerryJ allready knows how to handle several if's but don't know how to handle the Vlookups, I think my answer is fairly simple to deal with, at least for him

Personally I like Aladins answer (tread), becaurse it is well explained, but if you're allready into deep water, it might be best to stick with something you know.

Regards Tommy