Need help with formula for expressing multiple greater/less than

[charlie]

I am trying to construct a formula that will be based on the result of
an adjoining cell. I need to point to specific cell depending on what
the number is in that adjoining cell.

So for example:

If D1 = 0 or 1 - point to B21
If D1 = 2, 3, 4, or 5 - point to B22
If D1 = 6,7,8,9, or 10 - point to B23

and so on...

Rather than listing all of those variables, I know I should be able to
write it as equal to/greater than, but less than. I just don't know
how to express as a formula.

Thanks in advance for your help.

 

[Pete_UK]

Well, "and so on..." implies that you have other conditions, so it
might be better to think about using a lookup table and a VLOOKUP
formula, but for just the values you have quoted in your example, you
can do this:

=IF(D1<=1,B21,IF(D1<=5,B22,IF(D1<=10,B23,"and so on...")))

Note that in XL2003 and earlier there is a limit of 8 in the number of
times you can put IFs together in this way - hence a table might be
needed if you have more.

Hope this helps.

Pete

 

[Sheeloo]

IF will give allow you to have upto 8 conditions. If you have more then
Enter 0 in A21, 2 in A22 and 6 in A23
then use this formula
=VLOOKUP(D1,$A$21:$B$23,2,TRUE)
You can enter more values A23... down and extend the range in the formula
accordingly

 

[David Biddulph]

You haven't told us what to do if D1 is non-integer, or negative, or greater
than 10, but if none of those cases are possible you could use:
=IF(D1<=1,B21,IF(D1<=5,B22,B23))

 

[charlie]

Pete - your answer was perfect for my purposes. I don't expect that
there will be more 5-6 potential IF statements, so this works great.


Thanks to all for your responses and assistance.

 

[Pete_UK]

You're welcome, Charlie - thanks for feeding back.

Pete