Apologies in advance if I am in the wrong place to ask this question. Your suggestions for a more appropriate place to seek assistance are welcomed. Googling and searching the forum has not yielded any answers that seem to fit. I know my way around a spreadsheet but I think this formula is a bit out of my league.
I am creating a spreadsheet for a retiree on a fixed budget. He is a full-time RVer who has built a budget around an average price of $30/night for camping. He enters his actual price paid into the spreadsheet each night and we have a field that shows him his running average of prices paid.
When his average goes above his budgeted amount he wants to see how many nights of boondocking (free) or national campgrounds ($14/night) he needs to bring his budget back into line.
Goal Average = $30 (or less)
Current Average = sum of series of actual prices paid/count of # of nights camped
Corrective Rate = (enter either 0 or 14, depending on whether he is planning to boondock or use national parks)
Trying to solve for: #of values at Corrective Rate needed to be added to the series to achieve Goal Average
Thank you in advance for any assistance or direction on where to look for answers.
Danna
If there have been $n$ days with a total value of $S$, the average is $S/n$.
If you do $m$ more days with a value of $v$, the new average is $(S+mv)/(n+m) $.
To get this to equal a desired value $T$, we want $(S+mv)/(n+m) =T $ or $S+mv =(n+m)T =nT+mT $ or $S-nT =mT-mv =m(T-v) $ so $m =\dfrac{S-nT}{T-v} $.
You will have to figure out various conditions on the relative values of $S, T, $ and $v$.