Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I put more than 7 IF formula in a single cell?
There is a maximum set up for how many IF functions can be directly in a row
in a cell. My example: =IF(AND(B450,B45<1000,1,IF(AND(B451000,B45<3000, 2,IF.... I would like to continue this out to about 40, is this possible? or can I use some other formula to make this work correctly? |
#2
|
|||
|
|||
Try playin with this formula:
B1: =VLOOKUP(A1,{0,1;1000,2;3000,3;5000,4;8000,5},2,1) It uses a manually entered array, just like the vlookup function. Notice that there are commas and semicolons in the array. Type some value in A1 and the formula will return an approximate match from the Vlookup. Is that something you can use? Ron -- Regards, Ron "Antonio Mora" wrote: There is a maximum set up for how many IF functions can be directly in a row in a cell. My example: =IF(AND(B450,B45<1000,1,IF(AND(B451000,B45<3000, 2,IF.... I would like to continue this out to about 40, is this possible? or can I use some other formula to make this work correctly? |
#3
|
|||
|
|||
That seems to work just fine, thank you very much. I havent gone all the way
out to the numbers I need yet but its working past 7 :). Hopefully it will continue. Thanks lots Ron. "Ron Coderre" wrote: Try playin with this formula: B1: =VLOOKUP(A1,{0,1;1000,2;3000,3;5000,4;8000,5},2,1) It uses a manually entered array, just like the vlookup function. Notice that there are commas and semicolons in the array. Type some value in A1 and the formula will return an approximate match from the Vlookup. Is that something you can use? Ron -- Regards, Ron "Antonio Mora" wrote: There is a maximum set up for how many IF functions can be directly in a row in a cell. My example: =IF(AND(B450,B45<1000,1,IF(AND(B451000,B45<3000, 2,IF.... I would like to continue this out to about 40, is this possible? or can I use some other formula to make this work correctly? |
#4
|
|||
|
|||
Antonio,
Another option is to use binary switching formulas. They will work for numbers. However, text entries will not work. http://www.pdbook.com/index.php/exce...itch_formulas/ -- Regards, John Mansfield http://www.pdbook.com "Antonio Mora" wrote: That seems to work just fine, thank you very much. I havent gone all the way out to the numbers I need yet but its working past 7 :). Hopefully it will continue. Thanks lots Ron. "Ron Coderre" wrote: Try playin with this formula: B1: =VLOOKUP(A1,{0,1;1000,2;3000,3;5000,4;8000,5},2,1) It uses a manually entered array, just like the vlookup function. Notice that there are commas and semicolons in the array. Type some value in A1 and the formula will return an approximate match from the Vlookup. Is that something you can use? Ron -- Regards, Ron "Antonio Mora" wrote: There is a maximum set up for how many IF functions can be directly in a row in a cell. My example: =IF(AND(B450,B45<1000,1,IF(AND(B451000,B45<3000, 2,IF.... I would like to continue this out to about 40, is this possible? or can I use some other formula to make this work correctly? |
#5
|
|||
|
|||
John Mansfield wrote...
Another option is to use binary switching formulas. They will work for numbers. However, text entries will not work. .... Sure text will work, but it's wordier. LEFT(x,(y=z)*LEN(x)) is the text equivalent to the numberic x*(y=z) |
#6
|
|||
|
|||
No you cannot
Instead create table like this somewhere in the spreadsheet ad use vlookup to get teh results 0 1 1001 2 3001 3 4001 4 =vlookup(B45,$Y$1:$Z$100,2) assuming that your table is in the range Y1 to z100 where column Y holds the values in increments that you need and Column Z holds the result "Antonio Mora" <Antonio wrote in message ... There is a maximum set up for how many IF functions can be directly in a row in a cell. My example: =IF(AND(B450,B45<1000,1,IF(AND(B451000,B45<3000, 2,IF.... I would like to continue this out to about 40, is this possible? or can I use some other formula to make this work correctly? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Condition Sumif Formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
looking for a formula | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Formula Result Correct but value in the cell is wrong | Excel Worksheet Functions |