price calculations weighted values

Question

After a bit of advice on doing a calculator with a sliding scale.

I am building a matrix where we have set price points at intervals based on qty of items. The code I use works fine apart from the first 2 ranges.

Because for 1 qty the unit cost is so high my maths won't work.

Example Qty 1 = £23.25 (Price per unit is then £23.25) Qty 10 = £51.59 (Price per unit is then £5.159)

I then have further quantity's that work out correctly.

What I need to be able to do is some sort of weighted value, for 2 off the unit price needs to be near the £20 a unit mark, then 3 off less etc until I get to 10 off @ £5.159 a unit.

(It costs more for lesser quantity's, we want to encourage more qty)

Has anyone implemented something like this? From 10 qty onwards the calculation is fine as the unit cost changes are not much at all.

Thanks

Answer 1

You could use vlookup with a table as so:

Answer 2

Assuming you have quantities form 1 to 10, in column A, put 23.25 in B1 and 51.59 in B10, then the following formula in B2:

=B1+(B$10-B$1)/9

And populate down to B9

in C1 use the following formula:

=B1/A1

and populate down. Final result should look like this: