Excel Formula: How to calculate amount to add, given target average price

Question

Say I'm a potato trader. This is my transaction log so far:

Quantity: 2 Price: $2

Total cost: $4

Average Cost: $2

Potato is trading today at $0.5. Now I'm interested in bringing my average cost down to $0.75. My math isn't bad to the level where I can't solve this:

((2*2)+(x*0.5))/(2+x) = 0.75

Extracting x from here is easy. But how can I represent this formula in Google Sheets/Excel? I've tried:

=(((O3 * B3) + (N3))/P3)-B3

Where O3 is my curent average cost, B3 is my current quantity, N3 is the current potato price, and P3 is my target average price.

But the number I'm getting (how many more potatoes to buy at current market price to reach target average price) is off. What am I missing?

Answer 1

Lets take your equation for the average cost and solve it for x.

Let:
Q = current quantity
C = current average cost
P = current price
T = target average cost

Given:
((Q*C)+(x*P))/(Q+x) = T

Solve for x:
(QC+xP)/(Q+x)=T
QC+xP = T(Q+x)
QC = T(Q+x)-xP
QC = TQ + Tx -xP
QC -TQ = Tx - xP
Q(C-T)= xT - xP
Q(C-T) = x(T-P)
Q(C-T)/(T-P) = x

Substituting in your cell addresses should result in =B3(O3-P3)/(P3-N3)