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? |
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? |
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? |
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? |
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) |
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? |
All times are GMT +1. The time now is 09:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com