IF & AND Function
 

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(A ND(J7=3.00,J7<5.99,5.50,IF(AND(J7=6.00,J7<9.99,5 .25))))

Cell J7 is the cost.

Thanks
--
GRK

IF & AND Function
 

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.

--
Biff
Microsoft Excel MVP


"GRK" wrote in message
...
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(A ND(J7=3.00,J7<5.99,5.50,IF(AND(J7=6.00,J7<9.99,5 .25))))

Cell J7 is the cost.

Thanks
--
GRK

IF & AND Function
 

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

"GRK" wrote:

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(A ND(J7=3.00,J7<5.99,5.50,IF(AND(J7=6.00,J7<9.99,5 .25))))

Cell J7 is the cost.

Thanks
--
GRK

IF & AND Function
 

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


"GRK" wrote in message
...
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(A ND(J7=3.00,J7<5.99,5.50,IF(AND(J7=6.00,J7<9.99,5 .25))))

Cell J7 is the cost.

Thanks
--
GRK

IF & AND Function
 

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"GRK" wrote in message
...
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(A ND(J7=3.00,J7<5.99,5.50,IF(AND(J7=6.00,J7<9.99,5 .25))))

Cell J7 is the cost.

Thanks
--
GRK

IF & AND Function
 

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

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

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"GRK" wrote in message
...
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(A ND(J7=3.00,J7<5.99,5.50,IF(AND(J7=6.00,J7<9.99,5 .25))))

Cell J7 is the cost.

Thanks
--
GRK