Limit on logical function

[AJ]

I need to add 5 more conditions to my statement below. I have tried and I
can only enter 3 more before I get an error. Does anyone have any
suggestions for a work around or a better way to enter the formula?

=IF(AND(AH8<=10000,AH8>0),5,IF(AND(AH8>=10001,AH8<=25000),4,IF(AND(AH8>=25001,AH8<=50000),3,IF(AND(AH8>=50001,AH8<=100000),2,IF(AND(AH8=0),0,1)))))

 

[Mike H]

Hi,

Somewhere out of the way creat a table with your threshold values and what
you want to return similar to the table below. I used A1 - B5

Then the formula
=VLOOKUP(AH8,A1:B5,2,TRUE)
For any number uo to 10000 this will return zero and at 10001 it starts
returning 5. Add all the thresholds you want but ensure the left column must
remain sorted ascending.

0 0
10001 5
25001 4
50001 3
100001 2


Mike

 

[Max]

See whether this simpler VLOOKUP gives you the desired returns:
=IF(D1=0,0,VLOOKUP(D1,{1,5;1001,4;25001,3;50001,2;100001,1},2))
Expand the table array to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik

 

[AJ]

Thanks Max and Mike. Both worked great! Thanks for your time and prompt
response.

 

[Max]

Welcome. But pl take a moment to mark both responses by pressing the Yes
buttons (like the ones below)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik