Month to date (MTD) calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a worksheet consisting of two column. One is for the user to key in the daily revenue figure and the other is for the running month to date (MTD) figure. Is it possible for a user to just key in the daily revenue figure every day and have the MTD column automatically updated everytime the first column is changed. Is it also possible to have an automatically update year-to date (YTD) column as well

Daily Revenue Report for 17 March 200

Today MTD YT
Room revenue XX XX X
Food & beverage revenue XX XX X

I appreciate some help coz I'm really stuck.
 
Hi Aida

where are the MTD & YTD figures coming from now - another sheet or another
workbook?

Cheers
JulieD

Aida said:
I have a worksheet consisting of two column. One is for the user to key
Click to expand...
in the daily revenue figure and the other is for the running month to date
(MTD) figure. Is it possible for a user to just key in the daily revenue
figure every day and have the MTD column automatically updated everytime the
first column is changed. Is it also possible to have an automatically update
year-to date (YTD) column as well.
 
Sorry, I did not make myself clear. The mtd and ytd figures should come from the today column in the same worksheet. For example if i key in revenue as of 17 March 2004 of $1,000 (in the today column), the MTD column should automatically shows $1,000. When i key in revenue as of 18 March 2004 of $500 (in the today column) , the MTD column should automatically show $1,500 and so on. Therefore, everytime I update the today column (which is done daily), the MTD also updates for the new figures. Does that makes sense?.
 
Hi Aida

so if i'm understanding your correctly you OVERTYPE the "today" column each
day?
How many rows of data are we talking about?

Cheers
JulieD

Aida said:
Sorry, I did not make myself clear. The mtd and ytd figures should come
Click to expand...
from the today column in the same worksheet. For example if i key in
revenue as of 17 March 2004 of $1,000 (in the today column), the MTD column
should automatically shows $1,000. When i key in revenue as of 18 March
2004 of $500 (in the today column) , the MTD column should automatically
show $1,500 and so on. Therefore, everytime I update the today column (which
is done daily), the MTD also updates for the new figures. Does that makes
sense?.
 
Hi
nbot quite sure but give the following a try:
assumptions:
- Column A stores a date, column B your daily value and column C the
MTD figure
- row 1 is a heading row

Enter the following in C2
=IF(B2<>"",B2,"")

and in C3 enter
=IF(B3<>"",B3+C2,"")
copy this formula down

--
Regards
Frank Kabel
Frankfurt, Germany

Aida said:
Sorry, I did not make myself clear. The mtd and ytd figures should
Click to expand...
come from the today column in the same worksheet. For example if i key
in revenue as of 17 March 2004 of $1,000 (in the today column), the
MTD column should automatically shows $1,000. When i key in revenue
as of 18 March 2004 of $500 (in the today column) , the MTD column
should automatically show $1,500 and so on. Therefore, everytime I
update the today column (which is done daily), the MTD also updates for
the new figures. Does that makes sense?.
 
In continuation with Kabel; the other option will be to type the following in C2

=SUM(B$2:B2)

& drag it down

Regards
Sachin
 
You are right, I overtype the today column every day to input the daily revenue fgure. About twenty rows of data.
 
The thing is, I do not want to put in a date column, because i have about twenty data rows to key in daily. Therefore, I need to key in the data row (daily revenue figures for a hotel operations) in the same column (the today column) every day. Like, what JulieD said, I need to OVERTYPE the today column every day and my MTD column updated for the change i.e. previous day's balance+the today's figures.
 
Hi
so you kind of need an accumulator. See
http://www.mcgimpsey.com/excel/accumulator.html
for doing this.

BUT: I would suggest not doing this: You'll need the 'paper trail' for
your old values. And also what should happen if you enter a wriong
result. So best way:
- enter each day in a separate cell
- use Sorting, filtering, subtotals or pivot tables to create reports

--
Regards
Frank Kabel
Frankfurt, Germany

Aida said:
You are right, I overtype the today column every day to input the
Click to expand...
daily revenue fgure. About twenty rows of data.
 
The accumulator is exactly what i'm looking for although it doesn't sound like such a good thing to do. Thanks very much, Frank

Regards, Aida
 
Back
Top