IF & AND Function

[GRK]

I have a list of cost ranges and each range has a multiplier.

RANGE Multiplier
$0.00 to $0.99 6.00
$1.00 to $2.99 5.75
$3.00 to $5.99 5.50
$6.00 to $9.99 5.25

If a product has a cost range between 0.00 to 9.99, how do you write the
formula to pick the correct multiplier? If the product cost $2.50 then the
multiplier should be 5.75.

This is what I have and it's not working.

=IF(J7<=.99,6.00,IF(AND(J7>=1.00,J7<2.99,5.75,IF(AND(J7>=3.00,J7<5.99,5.50,IF(AND(J7>=6.00,J7<9.99,5.25))))

Cell J7 is the cost.

Thanks

 

[T. Valko]

Try this:

=LOOKUP(J7,{0,1,3,6},{6,5.75,5.5,5.25})

Note that any number greater than or equal to 6 will return 5.25. 1000000
will return 5.25.

 

[Greg Wilson]

Try:

=IF(J7<=0.99,6.00,IF(J7<2.99,5.75,IF(J7<5.99,5.50,IF(J7<9.99,5.25,1))))

You didn't say what multiplier applies if the cost >=$10.00. I assumed 1.
Change to suit. BTW, your formula didn't work because you left out the end
parentheses ")" after each of the AND functions.

Greg

 

[Rick Rothstein \(MVP - VB\)]

Don't you have the RANGE and Multiplier list you showed us on your worksheet
someplace? If so, wouldn't you want to reference them rather than hard code
their values into your formula? That way, if either the range or multiplier
values change, you just have to amend your lists and the formula would
update automatically. To implement this idea, I assumed the RANGE was in
Column A and the Multipliers were in Column, both lists starting in Row 2
(with Row 1 reserved for the header); J7 contains the cost to look up...

=SUMPRODUCT(B2:B5*(J7>=--LEFT(A2:A5,FIND("
",A2:A5)))*(J7<=--MID(A2:A5,FIND("to ",A2:A5)+3,9)))

Rick

 

[Dave]

Hi Biff,
I didn't know that you could put constants straight into a VLOOKUP formula.
Can't see that in the Help. It makes a very handy and compact stand-alone
formula when the lookup array is small.
Regards - Dave.

 

[T. Valko]

You can also use the same technique in a MATCH lookup:

=MATCH("x",{"x","y","z"},0)