Simulation problem

[Danio]

My problem is that I have installed crystal ball on my comp. When for
example I am trying to do the simulation I want one of the cells to be
decreasing gradually by the values given to me by the crystal ball.
That is not the case, lets say:

I have given number 3 by the simualtion

Lets say my inventory is 40

I want 40 to be decreasing every time a simulation gives me a number
but it is always subtructing from 40

and not 40-3-5-6-7-4-5

it is always 40-3, 40-5, 40-6

what should I change, is there a lock on the cell for that or what?

HELP PLEASE

 

[Gord Dibben]

Danio

Sounds like you want that cell to an accumlator cell.

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

 

[Otto Moehrbach]

I don't think you can do that with one cell without VBA. One way would be
to use 3 cells. The first holds 40. The second is where you type the
number you want to subtract from 40. The third gives you the answer. HTH
Otto