How do I put more than 7 IF formula in a single cell?

A

Antonio Mora

There is a maximum set up for how many IF functions can be directly in a row
in a cell. My example:
=IF(AND(B45>0,B45<1000,1,IF(AND(B45>1000,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?
 
R

Ron Coderre

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
 
A

Antonio Mora

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.
 
H

Harlan Grove

John Mansfield wrote...
Another option is to use binary switching formulas. They will work for
numbers. However, text entries will not work.
Click to expand...
....

Sure text will work, but it's wordier.

LEFT(x,(y=z)*LEN(x))

is the text equivalent to the numberic

x*(y=z)
 
N

N Harkawat

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
 
Top