Record multiple results of a formula calculation into a separate table

[Rich_B]

Hi, I would very much appreciate any assistance with the following problem:

I have a formula set up to display the result of the calculation in cell J2. Every time I hit F9 the formula recalculates and populates cell J2 with a different value. I would like to somehow automatically log each of these results elsewhere on my worksheet so I build up a record of historic results. Ideally the first result would copy from J2 to say L2. The next result would then again copy the new value from J2 to L3, the next result L4 and so on ad infinitum.

Is there an Excel function that enables me to do this? I have looked but can't seem to find one.

Many thanks

Rich

 

[Snakehips]

Rich,

Welcome to MrExcel.

You will not be able to do this with a formula but you may be able to do it with a little VBA.

Are you working with calculation set to manual?

 

[Rich_B]

Hi Tony

Thank you for the welcome.

I am somewhat of a beginner with Excel formulas, I have so far accumulated what limited knowledge I have from Google searches etc. Could you please clarify what you mean by calculation set to manual?

I am using Excel 2007 if that's relevant.

Thanks for your help.

Rich

 

[Snakehips]

Rich,

The reason I queried the calculation mode was because you say that you hit F9 to get J2 to re-calculate.

The default calculation mode for Excel is 'automatic' which means that it calculates automatically when a change is made to the worksheet that impacts on any formulas.
You can go into Excel Options and set for calculation to be 'manual'. When that is the case, you the user decides when to force a calculation by say hitting F9.

So, a couple of questions....
What is the formula you have in J2?
Is it a manual data input in that same sheet that causes a change of value in J2?
If so what cell(s) take the data change?
Does J2 re-calculate automatically when you make such a data entry? Or is it not until you hit F9?

 

[Rich_B]

Tony

Ah, yes I see what you mean now. The worksheet is set to automatic calculation but the only thing that changes is a distribution of 1000 randomly selected values from a pool of 50. As nothing actually changes within the formulas except these random variables I hit F9 to activate the next generation, thus creating a new value in J2 each time. I hope that makes sense.

To answer your questions:

- The formula I have in J2 is: =AVERAGE(C3:C1001) where the values in C3:C1001 are randomly generated from a pool of 50 using: =INDEX($A$3:$A$52,RANDBETWEEN(1,COUNTA($A$3:$A$52)))
- I 'think' I answered this in the above response but just to clarify, no, the value of J2 is changed based on the mean of 1000 values which are generated by a random number formula when I hit F9
- Cells C3:C1001 take the data change via the random number generating formula
- Nothing changes on the worksheet until I hit F9 or if I make a change to a cell and hit enter

Thanks

Rich

 

[Snakehips]

Rich,
Tanks for that. Now I understand why the F9 - because you are using RANDBETWEEN.
Normally, Excel is selective and only re-calculates formulas that relate to the changed data.
However, RANDBETWEEN is one of several functions that are said to be 'Volatile' in as much as they will recalculate at any and every opportunity regardless of what triggers the calculation event. Hence your values are changed by hitting F9.

The good news is that we can use code that is triggered by the calculation event to record the changes in L2.
The bad news is that it will record every change. Not just those that you instigate by F9 but others triggered by e.g. you change data in another sheet that triggers a calculation.


I would normally avoid Calculation Event code if at all possible because of this element of unpredictability.

Perhaps give this a try and see if it causes you any issues.
Right click your sheet tab >>> View Code >>>> you now see the vb editor window >>>> paste the below code into the white code pane >>> close the vb editor window.

Now see what happens when you F9.

If no issues then fine. If triggers other than F9 are a problem then there are perhaps things we can do.

Private Sub Worksheet_Calculate()
lr = Cells(Rows.Count, "L").End(xlUp).Row - 1
Application.EnableEvents = False
Range("L2").Offset(lr, 0) = Range("J2")
Application.EnableEvents = True
End Sub

When you save the workbook you will need to save it as a MacroEnabled Workbook

 

[Rich_B]

Wow Tony,

That's great. Thank you very much.

One small tweak if I may?

How do I adjust the cell where the values start from? They currently start at L2, I'd like them to start from I11.

Thanks

 

[Snakehips]

One small tweak it is........

Private Sub Worksheet_Calculate()lr = Cells(Rows.Count, "I").End(xlUp).Row - 10
If lr < 0 Then lr = 0
Application.EnableEvents = False
Range("I11").Offset(lr, 0) = Range("J2")
Application.EnableEvents = True
End Sub

Good luck with it.

 

[Rich_B]

Thanks Tony, you've saved me a lot of headaches there.

Just one more quick question:

- Is it possible to simulate say 10,000 presses of the F9 key? I want to be able to calculate the mean and standard deviation of the values produced after 10,000 generations. I don't fancy pressing F9 10,000 times.

Thanks

 

[Snakehips]

Rich,
Are you ok with having column I populated with the results of 10,000 calculations as and when you instigate it and otherwise, not populate column I?
ie do not record the result of single F9's or other normal calculation events.

BTW what the devil are you doing with this??