Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if statements, and statements
I have the following data:
QTY PRICE <100 100 100-500 95 501-1000 90 1000-2500 85 A function that I am looking for is if I type in a quantity of 550 in a cell, I want it to be able to retreive the correct pricing for whatever quantity I request. In this case I want it to give me a price of 95. But I also want to be able to use the same cell to request a quantity of 1000 and to be able to give me back the correct price of 85. Does anyone have a suggestion? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if statements, and statements
You can use VLOOKUP( ) for this. Type in your table somewhere, say L1
to M4, so it looks like this: 0 100 100 95 501 90 1000 85 If you use A1 to type in the quantity, then put this formula in B1: =VLOOKUP(A1,L$1:M$4,2) and this will give you the price from your table (Note your description is not consistent - 550 should have a price of 90. Also, the quantity 1000 is ambiguous - I've assumed 1000 and upwards gives a price of 85). Hope this helps. Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if statements, and statements
Suggestion
change your left hand column to show the bottom end of each range only, staring with 0, like so, in columns A & B Row 1: 0 100 Row 2: 100 95 Row 3: 501 90 Row 4: 1001 85 Then use the formula =VLOOKUP(550,$a$1:$b$4,2) BTW, 550 is priced at 90, not 95 "Sum Limit and marking" wrote: I have the following data: QTY PRICE <100 100 100-500 95 501-1000 90 1000-2500 85 A function that I am looking for is if I type in a quantity of 550 in a cell, I want it to be able to retreive the correct pricing for whatever quantity I request. In this case I want it to give me a price of 95. But I also want to be able to use the same cell to request a quantity of 1000 and to be able to give me back the correct price of 85. Does anyone have a suggestion? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if statements, and statements
=SUMPRODUCT(--(--LEFT(A2:A5,FIND("-",A2:A5)-1)<=C1),--(--MID(A2:A5,FIND("-",
A2:A5)+1,255)=C1),B2:B5) you will need to change the first range to 0-100, and have unique ranges that do not overlap. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Sum Limit and marking" wrote in message ... I have the following data: QTY PRICE <100 100 100-500 95 501-1000 90 1000-2500 85 A function that I am looking for is if I type in a quantity of 550 in a cell, I want it to be able to retreive the correct pricing for whatever quantity I request. In this case I want it to give me a price of 95. But I also want to be able to use the same cell to request a quantity of 1000 and to be able to give me back the correct price of 85. Does anyone have a suggestion? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UDFunctions and nested If-the-else statements | Excel Worksheet Functions | |||
Linking two IF statements together | Excel Discussion (Misc queries) | |||
Better Way to Code IF Statements? | Excel Discussion (Misc queries) | |||
Nested IF statements | Excel Worksheet Functions | |||
If statements | Excel Worksheet Functions |