Need help creating an algebra-like formula

[msdclark]

I wasn't good at Algebra because I should get this if I were.

I have the following:
V = number of bottles of product
W = price paid by customer
X = cost of bottle
Z = Free bottle

Normal price of bottle is 14.50
Customer needs to pay 14.00 a bottle
I can provide free bottles to make up the difference, but I can't lower price.

What is the ratio of bottles that need to be purchased along with number of free bottles I need to provide him to get the final bottle cost as close to 14.00 as possible?
It can be any ratio of purchased bottles to ratio of free bottles, but I can't figure out how to create a formula that lets me plug each of these amounts in.

The next time I'm faced with this situation the bottles will cost a different amount and the customer will pay a different amount.

 

[shg]

      --------A-------- --B--- -------C------- D
  1   List Price        $14.50                  
  2   Average Price     $14.00                  
  3   Free Bottle Every     28 B3: =B2/(B1-B2)

 

[Snakehips]

msdclark,

Perhaps this as a calculator....
Excel 2007

	A	B
1	Total bottles of product wanted	76
2	Customer's required bottle price	£ 14.00
3	Normal bottle price	£ 14.50
4	Bottles to be charged as normal	73
5	Bottles to be supplied free	3

<COLGROUP><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet3​

Worksheet Formulas Cell Formula B4 =B1-B5 B5 =B1-INT(B1*B2/B3) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Hope that helps.