Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Limit on logical function
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,AH80),5,IF(AND(AH8=10001,AH8< =25000),4,IF(AND(AH8=25001,AH8<=50000),3,IF(AND(A H8=50001,AH8<=100000),2,IF(AND(AH8=0),0,1))))) -- AJ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Limit on logical function
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 "AJ" wrote: 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,AH80),5,IF(AND(AH8=10001,AH8< =25000),4,IF(AND(AH8=25001,AH8<=50000),3,IF(AND(A H8=50001,AH8<=100000),2,IF(AND(AH8=0),0,1))))) -- AJ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Limit on logical function
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" wrote: 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,AH80),5,IF(AND(AH8=10001,AH8< =25000),4,IF(AND(AH8=25001,AH8<=50000),3,IF(AND(A H8=50001,AH8<=100000),2,IF(AND(AH8=0),0,1))))) -- AJ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Limit on logical function
Thanks Max and Mike. Both worked great! Thanks for your time and prompt
response. -- AJ "Max" wrote: 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" wrote: 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,AH80),5,IF(AND(AH8=10001,AH8< =25000),4,IF(AND(AH8=25001,AH8<=50000),3,IF(AND(A H8=50001,AH8<=100000),2,IF(AND(AH8=0),0,1))))) -- AJ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Limit on logical function
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 --- "AJ" wrote: Thanks Max and Mike. Both worked great! Thanks for your time and prompt response. -- AJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
logical function bug? | Excel Worksheet Functions | |||
Help with logical function | New Users to Excel | |||
Help with logical function | Excel Worksheet Functions | |||
should it be if() or another logical function? | Excel Worksheet Functions | |||
logical function | Excel Worksheet Functions |