Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |