Excel 2000

  sms 11:11 28 Nov 05
Locked

I am using an IF argument but can only use it 6 times.is there another way to try and increase this value.
My argument example is as follows:
=If(x=y,Vlookup(Table),If(x=z,Vlookup(Table etc etc

  MAJ 12:05 28 Nov 05

I think you need to use a lookup table rather than an IF statement, sms. Try installing (if you haven't already) the Lookup Wizard in Tools > Add-ins, then go to Tools > Lookup and follow the Wizard. VoG™ could explain it better than I could, so maybe best to wait for him to explain. This will bump you up the page, if nothing else.

  Simsy 20:10 28 Nov 05

that Excel can only EVALUATE 7 "ifs". However, there can be more than 7 in a formula... it depends on how the formula is constructed...

If you imagine a flow diagram, with "If" number 1 at the top... the possible outcomes of this "If" are "True" or "False". It can only be one or the other, by definition...

It is possible for "True" leg and the "False" leg to have a further 6, (or more), "If" statements. It is not possible for both side to need evaluating at once.

I have an Excel sheet that has a formula with 13 "If" statements. It took a lot of working out, using diagrams, to contruct the logic, and careful cut/pasting in stages to get it all right... but it does work.

Having said all that, generally speaking, it is not wise to have more than about 3 "If" statements in a single formula. It makes the formula unreadable and undecipherable. Usually a better job can be done using a lookup table of some kind. You seem to refer to a lookup in your formula so I suspect it can be better arranged.

click here is another forum specialising in Excel... but solutions are frequently, though by no means always, quite esoteric!

Good Luck,

Regards,

Simsy

  sms 14:17 29 Nov 05

Simsy,
thanks for info. Is it possible to e-mail your example so that I can see how to extend my formulas.

  Simsy 15:57 29 Nov 05

by my name, and send me your email address, indicating who you are...

I wont send you the file, because I promise you that you wont make sense of it. I'll send you another Excel file that has a diagram of how it's contstucted...

Just to prove it, the actual formula is;

=IF(OR(ISTEXT(E10),ISTEXT(F10),E10="",F10=""),0,IF(G10=0,0,IF((E10>F10),IF((E10<Lo),(Hi-Lo),IF((E10<Hi),IF((F10<Lo),((Hi)-E10),(F10-(Lo))+((Hi)-E10)),IF((F10>Lo),IF((F10<Hi),(F10-(Lo)),(Hi-Lo)),0))),IF((E10<Lo),IF((F10<Lo),0,IF((F10<Hi),(F10-(Lo)),(Hi-Lo))),IF((E10<Hi),IF((F10>Hi),((Hi)-E10),(F10-E10)),0)))))


Even I can't look at it and decipher it!!! (But as long as it works there's no need to change it!)... which is why it's recommended that you restrict the number of "ifs" to no more than about 3.

Regards,

Simsy

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

These are the Best Christmas Ads and Studio Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…