Multiple range formula
 

I'm looking for a formula that will give different result based upo
where the entry falls within a range.
Example.
If A1 is a number between 1-10 then it would return one possibl
result. (say, a cost of $5), but if it is in the range of 11-20 i
would return a different result (say, a cost of $10), and if it fall
within a range of 21-30 it would return a different cost (say, $20
etc.
A1 would be referencing another cell that has its own formula and tha
is where A1 gets its number.
I have tried using < and in forumalas but don't know how to construc
a formula that has both at the same time.
Any help would be very much appreciated

--
pdgoo
-----------------------------------------------------------------------
pdgood's Profile: http://www.excelforum.com/member.php...fo&userid=3162
View this thread: http://www.excelforum.com/showthread.php?threadid=51319

Multiple range formula
 

A pedantic way to do this is to use this formula:
=IF(AND(A1=1,A1<10),5,IF(AND(A1=10,A1<20),10,20) )

What that turns out to be is something like:
If a1 = 1 and a1 < 10 then 5
else if a1 = 10 and a1 <20 then 10
else 20

pdgood wrote:
I'm looking for a formula that will give different result based upon
where the entry falls within a range.
Example.
If A1 is a number between 1-10 then it would return one possible
result. (say, a cost of $5), but if it is in the range of 11-20 it
would return a different result (say, a cost of $10), and if it falls
within a range of 21-30 it would return a different cost (say, $20)
etc.
A1 would be referencing another cell that has its own formula and that
is where A1 gets its number.
I have tried using < and in forumalas but don't know how to construct
a formula that has both at the same time.
Any help would be very much appreciated.


--
pdgood
------------------------------------------------------------------------
pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623
View this thread: http://www.excelforum.com/showthread...hreadid=513199

Multiple range formula
 

If you have 29 values or fewer, you can try a choose statement like
this:

=CHOOSE(A1,5,5,5,5,5,5,5,5,5,5,10,10,10,10,10,10,1 0,10,10,10,20,20,20,20,20,20,20,20,20)
This says if a1 is 1 through 10, choose 5; if a1 is 11-20, choose 10;
if a1 is 21-29 choose 20.

But I suspect you'll have more than those.

Multiple range formula
 

Hello,

=LOOKUP(A1,{1,11,21,31},{5,10,20,"Too high"})

You may want to put the arrays into a cell range and refer to that
range. Its easier to maintain your worksheet...

HTH,
Bernd

Multiple range formula
 

Lots of great ideas, thanks!
Rajah, method one is what I had in mind, I will try it out when I get
home. Method two is interesting, but as you guessed I actually have
thousands of possibilities. The actual use of this cell is it returns
the number of square inches in a sign. (We make plaques and signs).
Pricing is based on square inches, but incredibly there is not a cost
per square inch - it's a bit of a sliding scale. So, someone made out a
chart of ranges.

Bplumhoff - your method looks really interesting, although it is a bit
over my head. What does the {} bracket tell Excel to do?
Many thanks!!


--
pdgood
------------------------------------------------------------------------
pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623
View this thread: http://www.excelforum.com/showthread...hreadid=513199

Multiple range formula
 

I tried extending the formula to include a third range of numbers but am
getting an error message. There are 11 ranges in all. It seems like this
ought to work, but I can't figure out where I went wrong. Any ideas?

=IF(AND(E22=1,E22<13),6.25, IF(AND(E22=13,E22<37), 9.38),
IF(AND(E22=37,E22<65), 15.63, 20))


--
pdgood
------------------------------------------------------------------------
pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623
View this thread: http://www.excelforum.com/showthread...hreadid=513199

Multiple range formula
 

first, you don't need the lower bound


=if(E22="","",if(E22<13,6.35,if(E22<37,9.38,if(E22 <65,15.63,20))))

just be aware that you can only nest 7 layers deep, so 11 ranges won't work
directly. You can split the ranges in half go down each side of an if
statement

=if(E22<150, - five lower conditions -, - six higher conditions - )

as an example.

--
Regards,
Tom Ogilvy




"pdgood" wrote in
message ...

I tried extending the formula to include a third range of numbers but am
getting an error message. There are 11 ranges in all. It seems like this
ought to work, but I can't figure out where I went wrong. Any ideas?

=IF(AND(E22=1,E22<13),6.25, IF(AND(E22=13,E22<37), 9.38),
IF(AND(E22=37,E22<65), 15.63, 20))


--
pdgood
------------------------------------------------------------------------
pdgood's Profile:
http://www.excelforum.com/member.php...o&userid=31623
View this thread: http://www.excelforum.com/showthread...hreadid=513199

Multiple range formula
 

Hello again,

The brackets {} define an array.

IMHO your problem asks for a table lookup solution, as shown in my
first answer. See
http://www.glencoe.com/ps/computered...?articleId=411 for
another example.

And: IMHO you should not use a "nested if" approach. But: Form your own
opinion, look at www.grbps.com/ExcelA.pdf for example.

HTH,
Bernd