On Monday, November 23, 2009 at 7:24:39 AM UTC+3, David Biddulph wrote:
If you want to make the relationship between selling price and quantity a
linear relationship, then you could put your quantities of 10 and 100 units
in A2 and A3, and the corresponding unit prices of $10 and $5 in B2 and B3.
Then if your quantity of 59 is in A4, the price per unit would be
=B$2+(A4-A$2)*(B$3-B$2)/(A$3-A$2)
and the total for A4 units at that price would be
=A4*(B$2+(A4-A$2)*(B$3-B$2)/(A$3-A$2))

Another way of getting the same result is =FORECAST(A4,B$2:B$3,A$2:A$3) for
the unit price or =A4*FORECAST(A4,B$2:B$3,A$2:A$3) for the total.

One thing you will notice is that with that method of calculating your
discount the number for 90 units would be $500, which is the same as for 100
units, and numbers between 90 and 100 would cost slightly more than for 100.
Another danger is that if you tried to continue to use the same formula
beyond 100 units, you'd get to a zero selling price at 190 units, and you'd
be paying the customer to take the product away for 190 units.
--
David Biddulph

"dgold82" wrote in message
...
I am looking for a business formula: Basically, I am trying to figure out
the
best bulk discount per unit as possible to maximize profit.

For example: 10 units would sell for $10 dollars each. 100 units would
sell
for $5 dollars each.

If I left a cell blank for units and put it 59 I want excel to calculate
what the cost would be based on the above range ($10 - $5) on a sliding
scale
for discount. This might be simple but I am having a hard time wrapping my
brain around the formula.

Thank!.

How i wish you also posted the actual formula without excel!