accumulator with other than one

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan - 22 Nov 2006 06:46 GMT

All -

I am trying to figure out how to do a accumulator to an existing report that
has numbers in it already and want to update from those numbers...

example [3456] [2123] updater cell > [412]
the two above change as I update the other from this point....

any help is greatly appreicated.....thanks

V/r MSgt ANDERS
OEF Afghanistan

Reply to this Message

Niek Otten - 22 Nov 2006 09:56 GMT

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

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| All -
|
[quoted text clipped - 8 lines]
| V/r MSgt ANDERS
| OEF Afghanistan

Reply to this Message

Dan - 22 Nov 2006 10:51 GMT

Thanks sir - could you perhaps explain this a little, I am really new and
need some deciphering help..

Thanks

V/r MSGT ANDERS

> http://www.mcgimpsey.com/excel/accumulator.html
>
[quoted text clipped - 10 lines]
> | V/r MSgt ANDERS
> | OEF Afghanistan

Reply to this Message

Niek Otten - 22 Nov 2006 11:05 GMT

Following the link in the right hand margin to "Getting started with macros..."

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

Reply to this Message

Gord Dibben - 22 Nov 2006 19:55 GMT

Dan

As Niek points out...it can be done.

But what happens if you make a mistake in data entry?

You have no "paper trail" to follow to troubleshoot entries.

Gord Dibben MS Excel MVP

>All -
>
[quoted text clipped - 8 lines]
>V/r MSgt ANDERS
>OEF Afghanistan

Reply to this Message

Niek Otten - 22 Nov 2006 21:37 GMT

Hi Gord,

I agree with you entirely. So does John McGimpsey, apparently; this is what he states on his site:

<I dislike accumulators on a spreadsheet, primarily because they retain no history - if an error is made, it may be impossible to
track down in an accumulator, where if the numbers were entered sequentially in a column of cells, they could be examined and
corrected>

But the question keeps coming back in the newsgroups!

Since John's warning is included in the link, I just give the link.

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Dan
|
[quoted text clipped - 18 lines]
| >V/r MSgt ANDERS
| >OEF Afghanistan

Reply to this Message

Gord Dibben - 22 Nov 2006 23:03 GMT

Thanks Niek

I had not been to John's site for this function for a while so had forgotten his
caveat about accumulator cells.

Gord

>Hi Gord,
>
[quoted text clipped - 7 lines]
>
>Since John's warning is included in the link, I just give the link.

Gord Dibben MS Excel MVP

Reply to this Message

Dan - 24 Nov 2006 04:24 GMT

Niek and Gord - thanks but I really don't understand all the terminology - I
am just looking for the secret starter to the formula so I can start from a
different number other than 0 or 1 and keep it going.

The trail thing is an issue I agree, but the report is updated and saved
under different name each time.....thanks for all and any future help
gentlemen.

V/r MSGT DAN ANDERS

> Dan
>
[quoted text clipped - 18 lines]
> >V/r MSgt ANDERS
> >OEF Afghanistan

Reply to this Message

Gord Dibben - 24 Nov 2006 18:29 GMT

Dan

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

Gord

>Niek and Gord - thanks but I really don't understand all the terminology - I
>am just looking for the secret starter to the formula so I can start from a
[quoted text clipped - 28 lines]
>> >V/r MSgt ANDERS
>> >OEF Afghanistan

Gord Dibben MS Excel MVP

Reply to this Message