Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hello,
I have a matrix as under: Days <30 day <60 days <90 days Units 0-100 $20 $30 $40 100-200 $15 $25 $35 201-300 $12 $22 $32 This shows if my order is between 0-100 units and if I pay in less than 30 days then my price of the product would be $20. Further, if my order is between 0-100 and if I pay in less that 90 days then my value of the product would be $40. Can I have a formula where in a column I put my value and no. of days payment and the formula will get me the price/value or the product from this matrix. For e.g. A1 column I put the no. of unit I want (let's say 25 units) and in B1 column I put the no. of days I would pay in (let's say 45 days), and in C1 I get the price as ($30 x 25 units). That means C1 would have a formula. Hope I made my question clear. I tried to do it by lookup function but without success. Please advise. Thanks in advance. Dinesh |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi,
For a table lookup you can use this =INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0)) Where A1:E20 is the entire table F1 is the value to find in the column G1 is the value to find in the row The intersect of the 2 is returned -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "nsd" wrote: Hello, I have a matrix as under: Days <30 day <60 days <90 days Units 0-100 $20 $30 $40 100-200 $15 $25 $35 201-300 $12 $22 $32 This shows if my order is between 0-100 units and if I pay in less than 30 days then my price of the product would be $20. Further, if my order is between 0-100 and if I pay in less that 90 days then my value of the product would be $40. Can I have a formula where in a column I put my value and no. of days payment and the formula will get me the price/value or the product from this matrix. For e.g. A1 column I put the no. of unit I want (let's say 25 units) and in B1 column I put the no. of days I would pay in (let's say 45 days), and in C1 I get the price as ($30 x 25 units). That means C1 would have a formula. Hope I made my question clear. I tried to do it by lookup function but without success. Please advise. Thanks in advance. Dinesh |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Dinesh -
There are a couple approaches, depending on how flexible this will need to be and how much the matrix can change. You also didn't say if there is a different price point for those ordering over 300. I assume you don't allow anyone to choose to pay over 90 days. One way to do this is to change the headings on the matrix so that they are numbers that can be used in determining the eligibility. Assuming this matrix is in cells A8 to D11 of your worksheet, change the cells A9 to A11 to be the upper limit of quantity, and the cells B8 to E8 to be the upper limit of number of days to pay. Like this: 30 60 90 100 20 30 40 200 15 25 35 300 12 22 23 If your quantity is in cell A1 and the days to pay would be in B1, then your formula for calculating the total price in cell C1 would be: =IF(A1<=$A$9,IF(B1<$B$8,$B$9,IF(B1<$C$8,$C$9,$D$9) ),IF(A1<=$A$10,IF(B1<$B$8,$B$10,IF(B1<$C$8,$C$10,$ D$10)),IF(B1<$B$8,$B$11,IF(B1<$C$8,$C$11,$D$11)))) * A1 A couple things to note - if you choose 100 items (or less), it goes with the prices on row 9, for quantities up to 100. If you choose anything over 200 items, it goes with the prices on row 11. If you choose 30 days, it goes with the prices in column C (not B because B was < 30 days). If you want the 30 days to be included in column B pricing (e.g. <= 30 days), then you need to change the formula above for every "B1<" to "B1<=". This also assumes any number of days 60 or more will use the prices in column D, so if someone entered 300 days to pay, it would use the prices for 90 days or less. That said, the matrix is updateable so that if you want to change the prices you can. If you want to change the breakpoints for the number of days or quantity, you can, and the formula will take into account the new numbers. The formula does not take into account increasing the size of the matrix to include additional breakpoints. If you think that will change, then you may want to use VBA code to drive this instead of a formula. Good luck! -- Daryl S "nsd" wrote: Hello, I have a matrix as under: Days <30 day <60 days <90 days Units 0-100 $20 $30 $40 100-200 $15 $25 $35 201-300 $12 $22 $32 This shows if my order is between 0-100 units and if I pay in less than 30 days then my price of the product would be $20. Further, if my order is between 0-100 and if I pay in less that 90 days then my value of the product would be $40. Can I have a formula where in a column I put my value and no. of days payment and the formula will get me the price/value or the product from this matrix. For e.g. A1 column I put the no. of unit I want (let's say 25 units) and in B1 column I put the no. of days I would pay in (let's say 45 days), and in C1 I get the price as ($30 x 25 units). That means C1 would have a formula. Hope I made my question clear. I tried to do it by lookup function but without success. Please advise. Thanks in advance. Dinesh |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hey Mike,
Sorry, it didn't work. I think since I have a quantity figure as mentioned in my question i.e. 25 which falls between 0-100, and days i.e. 45 which is < 60 days as per matrix, this is creating an error. Please advise. Dinesh "Mike H" wrote: Hi, For a table lookup you can use this =INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0)) Where A1:E20 is the entire table F1 is the value to find in the column G1 is the value to find in the row The intersect of the 2 is returned -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "nsd" wrote: Hello, I have a matrix as under: Days <30 day <60 days <90 days Units 0-100 $20 $30 $40 100-200 $15 $25 $35 201-300 $12 $22 $32 This shows if my order is between 0-100 units and if I pay in less than 30 days then my price of the product would be $20. Further, if my order is between 0-100 and if I pay in less that 90 days then my value of the product would be $40. Can I have a formula where in a column I put my value and no. of days payment and the formula will get me the price/value or the product from this matrix. For e.g. A1 column I put the no. of unit I want (let's say 25 units) and in B1 column I put the no. of days I would pay in (let's say 45 days), and in C1 I get the price as ($30 x 25 units). That means C1 would have a formula. Hope I made my question clear. I tried to do it by lookup function but without success. Please advise. Thanks in advance. Dinesh |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi,
Now I understand but it's now a little more complex. Try this ARRAY formula. See below on how to enter an array formula =INDEX(A1:E20, MATCH(F1,A1:A20,0),MATCH(INDEX(A1:E1,MATCH(MIN(IF( A1:E1=G1,A1:E1)),A1:E1,0)),A1:E1)) Where your table is A1:E20 F1 is the coluumn lookup G1 is the row lookup Now if an exact match isn't found in the row it returns the intersect of the higher value. One point is the header row must contain number and NOT <20, <40 <60 etc. It must be 20, 40, 60 etc This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "nsd" wrote: Hey Mike, Sorry, it didn't work. I think since I have a quantity figure as mentioned in my question i.e. 25 which falls between 0-100, and days i.e. 45 which is < 60 days as per matrix, this is creating an error. Please advise. Dinesh "Mike H" wrote: Hi, For a table lookup you can use this =INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0)) Where A1:E20 is the entire table F1 is the value to find in the column G1 is the value to find in the row The intersect of the 2 is returned -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "nsd" wrote: Hello, I have a matrix as under: Days <30 day <60 days <90 days Units 0-100 $20 $30 $40 100-200 $15 $25 $35 201-300 $12 $22 $32 This shows if my order is between 0-100 units and if I pay in less than 30 days then my price of the product would be $20. Further, if my order is between 0-100 and if I pay in less that 90 days then my value of the product would be $40. Can I have a formula where in a column I put my value and no. of days payment and the formula will get me the price/value or the product from this matrix. For e.g. A1 column I put the no. of unit I want (let's say 25 units) and in B1 column I put the no. of days I would pay in (let's say 45 days), and in C1 I get the price as ($30 x 25 units). That means C1 would have a formula. Hope I made my question clear. I tried to do it by lookup function but without success. Please advise. Thanks in advance. Dinesh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Name Matrix | Excel Discussion (Misc queries) | |||
How can I transpose nXm matrix to mXn Matrix | Excel Worksheet Functions | |||
Matrix | Excel Worksheet Functions | |||
matrix | Excel Worksheet Functions | |||
Matrix | Charts and Charting in Excel |