[SOLVED] Excel Formatting Recap

bradenterpstra01

Prominent
Mar 22, 2020
22
2
515
I am a student wanting to make an Excel sheet for finding how many dining dollars (money on student ID card) I can spend each week. I know I could just use a calculator but wanted to make an Excel sheet for practice. Here is what I need it to do:



I have 200 dining dollars to spend this semester. There are 15 weeks remaining in the semester. That means I can use ~$13.33 each week on extra food. Now, I don't eat much extra, so I won't spend 13 a week. So, each week, the total would need to be recalculated by dividing the current amount remaining by the number of weeks remaining.



So, in other words, at 15 weeks, I can spend 13.33 a week. If I only spend 5 dollars that week, that means the new total is 195 and at 14 weeks remaining, I can spend 13.92 a week from that point on and so on and so on.



How can I automate that in Excel?
 
Solution
Clunky, but it works

Column A: Week # (Static)
Column B: Dining Dollars spent each week (manually entered)
Column C: Dining Dollars Remainder (=sum(C2, -B3) Incremented for each week)
Column D: Weekly Average Remaining (=C3/14 incremented weekly so the next row reads =C4/13, =C5/12, and so on) You can calc this as well.

Once the formulae are set, all you need to do is enter the weekly dollars spent and the rest calculates.

-Wolf sends

Wolfshadw

Titan
Moderator
Clunky, but it works

Column A: Week # (Static)
Column B: Dining Dollars spent each week (manually entered)
Column C: Dining Dollars Remainder (=sum(C2, -B3) Incremented for each week)
Column D: Weekly Average Remaining (=C3/14 incremented weekly so the next row reads =C4/13, =C5/12, and so on) You can calc this as well.

Once the formulae are set, all you need to do is enter the weekly dollars spent and the rest calculates.

-Wolf sends
 
Solution
Another idea

Column A = week number or week ending/start date
Column B = weekly budget
Column C = actual spend
Column D = (Total of column B - Total of column C) / CountA of remaining weeks in column A.

To count remaining weeks and be able to copy the formula down use for example A4:A$15 where row 4 is the current row and row 15 is the end week/row.
 

USAFRet

Titan
Moderator
Is it a one time influx of $200, or is it a weekly addition of $13.33?
Daily expenditure or weekly tracking? (do you remember what you spent last Wednesday?)


hlTTd2H.png


The next Col/Row 6 = the Previous Col/Row 10.
Graph from 1 Sept to Dec 31.
 

bradenterpstra01

Prominent
Mar 22, 2020
22
2
515
Clunky, but it works

Column A: Week # (Static)
Column B: Dining Dollars spent each week (manually entered)
Column C: Dining Dollars Remainder (=sum(C2, -B3) Incremented for each week)
Column D: Weekly Average Remaining (=C3/14 incremented weekly so the next row reads =C4/13, =C5/12, and so on) You can calc this as well.

Once the formulae are set, all you need to do is enter the weekly dollars spent and the rest calculates.

-Wolf sends
Can you provide an example file or something? I could not get the formulas to work properly. Thanks!
 

TRENDING THREADS