If(and) Formula - Problems with number of arguments 1

[Suggie]

Morning,

I have the below formula in a cell, basically an if(and) formula with 7 scenarios. I want to add on another 6 scenarios within the same formula e.g. 60%-80%, 80%-100% etc.

When I add in more arguments I get the message that there is an error in the formula. Is there a limit in the cell?

Any ideas?

TIA,
Mark

=IF(AND(M163>5%,M163<7%),"a. 5%-7%",IF(AND(M163>7%,M163<10%),"b. 7%-10%",IF(AND(M163>10%,M163<15%),"c. 10"%-15%,IF(AND(M163>15%,M163<20%),"d. 15%-20%",IF(AND(M163>20%,M163<25%),"e. 20%-25%",IF(AND(M163>25%,M163<40%),"f. 25%-40%",IF(AND(M163>40%,M163<60%),"g. 40%-60%")))))))

 

[GlennUK]

You cannot next IF functions more than 7 deep.

Use something like this instead :

=IF(AND(M163>5%,M163<7%),"a. 5%-7%","")&IF(AND(M163>7%,M163<10%),"b. 7%-10%","")&IF(AND(M163>10%,M163<15%),"c. 10%-15%","")&IF(AND(M163>15%,M163<20%),"d. 15%-20%","")&IF(AND(M163>20%,M163<25%),"e. 20%-25%","")&IF(AND(M163>25%,M163<40%),"f. 25%-40%","")&IF(AND(M163>40%,M163<60%),"g. 40%-60%","")

which concatenates the results of multiple separate IF functions.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

Better yet, have a look at VLOOKUP, utilising the 4th argument set to TRUE (ie finding near matches)

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[Suggie]

Good idea, thanks, the vlookup works perfect.