Getting a value out of a Matrix

[nsd]

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

 

[Mike H]

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.

 

[Daryl S]

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!

 

[nsd]

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]

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.