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

 

[Rich_B]

Tony,

I think I would like to retain the 10,000 values just so that I can use them to plot graphs. It might be wise however, to move them from column I and put them in an unused column off to the far right so they're out of the way. Say column AJ starting at row 3?

Single F9's would then be obsolete so column I can remain clear.

My have been trading financial markets part-time for several years, I am soon to be leaving my current career to trade full-time. Before I do this I want to be able to do extensive simulated testing of my various systems to determine which will be the most profitable under more active trading conditions as my trading up until this point has been in the evenings only after work when the markets are quiet. It's a whole different ball game day trading through the London session and particularly the London/Wall Street crossover.

 

[Snakehips]

In that case do you really need column I?
Can each set of results not just go to AJ3 or the next available column?

Is this going to make you rich Rich?

 

[Rich_B]

I don't need column I to do anything now but it does have data in it on the higher rows so I can't delete it. But yes, if the sets of 10,000 results can go straight into column AJ3 that would be ideal. Then I can set up a simple formula to calculate the mean and median values from the range of results.

I'm not sure if it's going to make me rich but it'll make me a living at least.

Thanks

 

[Snakehips]

Maybe try this then......

We no longer need to be using the Calculate event so remove the previous code.

Paste this in instead.

Sub TenK_Calculate()
Application.ScreenUpdating = False
lc = Cells(3, Columns.Count).End(xlToLeft).Column + 1
If lc < 36 Then lc = 36
Application.Calculation = xlCalculationManual
For i = 1 To 10000
Application.Calculate
Cells(i + 2, lc) = Range("J2")
Next i
Application.Calculation = xlCalculationAutomatic
End Sub

Although your random numbers will still generate a new set of numbers each time excel performs a 'natural' calculation, they will not be recorded.

However, when you run this code it will record a set of 10000 results. Initially to AJ3 :AJ10003 and subsequently to the next clear column on the right.

You may wish to assign the code to a shape or a keyboard shortcut in order to run it.

 

[Rich_B]

Thanks Tony,

How do I execute the code?

I've copied the worksheet to a new tab and pasted in the code to the empty box but nothing new happens when I hit F9.

Thanks

Rich

 

[Snakehips]

Post #14 ..... You may wish to assign the code to a shape or a keyboard shortcut in order to run it.

F9 is now redundant.
It will force a re-calculation but it will not be recorded.

You can just run from the View tab >> Macros section >> View Macros >> Select your macro >> Run

Or best bet is to set up a button on your sheet.

Insert tab >>> Shape of your choosing >> Add text if you wish

Right click shape >> Assign Macro >>> Select Macro >>> ok

Then click shape runs macro.

 

[Rich_B]

Thanks Tony, you've been a star.

I really appreciate your time and expertise.

Thanks again.

Rich

 

[Snakehips]

You are welcome. Remember me when you hit the big time.

 

[Rich_B]

I certainly will do.

I've noticed that now on the previous worksheet the function where I was pressing F9 to generate single results has stopped working even though the code is still there. Is it possible to reactivate this as I would still find it useful to have?

Thanks

 

[Rich_B]

Got it working again now.

Thanks.