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!