Returing a value using multiple range conditions
Posted by Mark on February 07, 2002 6:40 PM
I am trying to figure out how I would write a formula to do do the following;
I am looking for excel to automatically populate a cell with a fee amount based on the cost of an item. For example,
If the cost of an item is greater than .01 and less than 9.99, then the fee = .30
if the cost of an item is greater than 10 and less than 24.99, then the fee = .55
if the cost of an item is greater than 25 and less than 49.99, then the fee = 1.10
Posted by anno on February 07, 2002 6:59 PM
mark
think this will work but note that returns #N/A if referent cell is blank or less than .01:
=VLOOKUP(A1,{0.01,0.3;10,0.55;25,1.1},2)
come to think of it, doesn't meet your >25<49.99 condition either, but a start anyway.
Posted by anno on February 07, 2002 7:09 PM
this is uglier but meets all your needs i think...
ERR
Posted by anno on February 07, 2002 7:20 PM
less ugly - have switched brain on now...
sometimes it takes a while, but i think this is it:
=VLOOKUP(A1,{0,0;0.01,0.3;10,0.55;25,1.1;50,"Out of range"},2)
Posted by Mark on February 07, 2002 7:21 PM
Anno,
Thanks. I got it to work.
Posted by Mark on February 07, 2002 7:22 PM
Anno,
Thanks. I got it to work.
