simple excel question

  • Thread starter Thread starter Cletus Stripling
  • Start date Start date
C

Cletus Stripling

I think this should be a simple question but for the life of me can't
determine the best way to do it.

I want to have a spreadsheet where the daily totals are entered to keep a
running month to date and year to date total. I don't care about
maintaining the daily input numbers.

For example,

Today's data MTD YTD
10 10 10

Now for tomorrow
20 30 30
 
And where is a date entered to determine which date is to be considered for
MTD?
 
Cletus

Simple question with complex results.

You can have a cumulative total in a cell if you have a
separate source cell for adding a new total to the original.

Use at your own risk. I am Posting this just to show you how it can
be done, not as a good solution. You would be much better off to
have another column so you can keep track of past entries.

Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)

Enter this in cell D4 and then in Tools>Options>Calculation check
Iterations and set to 1.

Now when you change the number in C4, D4 will accumulate.

Note 1. If C4 is selected and a calculation takes place anywhere in
the Application D4 will update even if no new number is entered in
C4. NOT GOOD.

Note 2. This operation is not recommended because you will have no
"paper trail" to follow. Any mistake in entering a new number in C4
cannot be corrected. NOT GOOD.

To clear out the accumulated total in D4 and start over, select D4
and Edit>Enter.

Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
which does what you want without the re-calculation problem, but
again there is no "paper trail" for back-checking in case of errors
in data input.

http://longre.free.fr/english/func_cats.htm

Having said that, check out J.E. McGimpsey's site for VBA methods.

http://mcgimpsey.com/excel/accumulator.html


Gord Dibben Excel MVP
 
We decided to use the accumulator method and have this working now but we
also need to calaculate a daily average.

example:

1st days data MTD Daily Average

5 5 5

2nd days data MTD Daily Average

7 12 6 (this would
be the second day divisible by 2)

3rd days data MTD Daily Average

3 15 5 (this would
be the third day divisible by 3) ... and so on


What we want is to use the daily data as an input and that be the only
column that is changed on a daily basis. The other columns would calculate
for you throughout the month as the days data is changed.

Thanks.
 
Back
Top