We have 3 transactions of an item purchased at different prices . The current market price is 10.00 for a total of 6 items with a cost avg of 33.33. (Total investment divided by Quantity)
Is there a formula I can enter in excel or Sheets to spit out the additional quantity I would need to buy at the current price to get my cost avg to equal as close to the current price (within a whole dollar, don't care about the cents)
Currently I have to plug in random numbers to see how close I can get.
2 Examples: If the Current Price is 10.00 the answer is 135 more to bring the cost avg down to 10.99 If the Current Price is 20.00 the answer is 75 more to bring the cost avg down to 20.99
It doesn't have to be within 99 cents it can be closer its just important to specify as close to a dollar otherwise the calculation would be too large just to save a few pennies.
For example buying qty. 10,000 would bring cost avg to 20.01 at a total cost of $200K vs just buying qty. 75 for cost avg 20.99 which only cost $1,500
Note: I'm not sure why this is so hard to convey or find a solution. Its similar to the opposite scenario of having to figure out how many tests you need to score a 100 to get your avg score to equal blah blah.
