IF / AND Limitations ?

[mikeprice53]

Hi I am using Office XP ( 2002 ) and in excel I want to use the following formula

=IF(J2<19.99,1,IF(AND(J2>=20,J2<=39.99),2,IF(AND(J2>=40,J2<=59.99),3,IF(AND(J2>=60,J2<=79.99),4,IF(AND(J2>=80,J2<=99.99),5,IF(AND(J2>=100,J2<=119.99),6,IF(AND(J2>=120,J2<=139.99),7,0)))))))

only thing is that is the maximum ( I think ) amount of If I can use at once If I am correct as when I try to go on with it ( There are 16 parts to it not just eh seven stated there ) it highlights my last AND and tells me the formula is incorrect when looking and looking very thoroughly at it ?

Basically I am doing a price breakdown into 18 catagories and the formula you see has teh first 7 in it... the list I use is this:

0-19.99
20-39.99
40-59.99
60-79.99
80-99.99
100-119.99
120-139.99
140-159.99
160-179.99
180-199.99
200-249.99
250-299.99
300-349.99
350-399.99
400-499.99
500-699.99
700-999.99
1000+

Im sure you see the way I was going about this. Is there a limit to the amount I get to use or not ?

Please help...Oh and Thanx :0)

 

[inarbeth]

You are better served using VLOOKUP. It is clearer than your lengthy formula and easier to edit if you need to change anything.

 

[mikeprice53]

hmm was thinking of using it that way but wanted to do a direct formula to it to save me problems as have done a simialr task with vlookup for this sort of project before and... well it got messy once the project progressed.

Thanks for the advice though.

 

[fairwinds]

Hi,

Try:

=MATCH(J2,{0,20,40,60,80,100,120,140,160,80,200,250,300,350,400,500,700,1000},1)

 

[mikeprice53]

=MATCH(J2,{0,20,40,60,80,100,120,140,160,80,200,250,300,350,400,500,700,1000},1)

whoa ...

Ill be using that again for sure :0) thank you

 

[fairwinds]

Please note the small typing error

=MATCH(J2,{0,20,40,60,80,100,120,140,160,180,200,250,300,350,400,500,700,1000},1)

should be correct.

 

[mikeprice53]

np I did see that and corrected it - just didnt want to be sounding like a know it all and tell you there was a typo

thank you anhow my friend :0)