Pricing calculation based on quantity packs

Hi everyone,

I've been searching everywhere for a solution to this, but haven't had much luck yet. Am sure there is an easy solution.
This is kind of like a reverse progressive pricing scenario.

We purchase an IT product which is sold in a variety of license quantities packs. Example as follows:

1 unit - $750
10 pack $7200
25 pack $17500
50 pack $33500
75 pack $42750

I would like to build a calculator, where I can type in the exact quantity the client needs, and it will calculate the price, based on the "packs" above.

So if a client needs 36 units, it would be 1 x 25 pack, 1 x 10 pack, 1 x 1 unit. total $25450.

If a client needed 68 units, it would be 1 x 50 pack, 1 x 10 pack, 8 x 1 unit.

Most of the calculations I've found online work progressively... E.g. For 36 units, it would do 9 units at the lowest price ($750), then 10 units at $720 ($7200/10), then 17 units at $700 (17500/25) giving a total of $25850.00.

Hope someone out there can assist with this one!

Cheers
Aaron

Hi Aaron. Something like this should work.

Basically a table of what you listed above (units & price), together with some ROUNDDOWN(Units required / size,0) functions should get what you want. The total price is then calculated using a SUMPRODUCT.

OR,

See the attached file! I have used a supporting table with LOOKUP function here.