Excel rubbish

[Neutrideo]

Hey, i have this spreadsheet, and i have a question. Is there a way where i can have this running, but a program reads the data in cell g4 each time the spreadsheet generates a new set of data and dumps the average, maybe over 1000 into a cell in the file.

[MrMarriarty]

well yes you can do this kind of thing... can you post a bit more detail like and example and what program you want to access the data

[Neutrideo]

7 minutes ago, MrMarriarty said:

well yes you can do this kind of thing... can you post a bit more detail like and example and what program you want to access the data

I have an average stock return rate over 30 years, i want to have excel do f9 hundreds of times and a program to read the data and make an average of the return rates. 

[MrMarriarty]

ok so it sounds like you don't have anything in existence that does this, it's all manual at the moment and you want to automate it?

 

If that's the case and you have the data within excel you can do it all in there using some fancy formulas and possibly a bit of Visual basic, for further info please provide an actual example of the process that needs to happen with some data

[Neutrideo]

Just now, MrMarriarty said:

ok so it sounds like you don't have anything in existence that does this, it's all manual at the moment and you want to automate it?

 

If that's the case and you have the data within excel you can do it all in there using some fancy formulas and possibly a bit of Visual basic, for further info please provide an actual example of the process that needs to happen with some data

Im just learning all of this, i dont particurally understand how to explain it, have you loooked at my spreadsheet??

[MrMarriarty]

Just now, Neutrideo said:

Im just learning all of this, i dont particurally understand how to explain it, have you loooked at my spreadsheet??

id happily have a look but i cant see a link to it anywhere... have i missed it? also make saure its ok for you to be sharing the exact data not a sample set i dont want you getting in troubble

[Neutrideo]

So, this is my spreadsheet, i want the Average return over 30 years to be calculated many times, then a program reads this and calculates the average.

[Neutrideo]

Let me upload a new one

[Neutrideo]

https://www.mediafire.com/file/vb30s14ppd7b69x/Monte carlo simulation 1.xlsx

[Neutrideo]

This is just a simulation, but i want as realistic as possible outcome, and everything in front of me, i need to do a assesment on this in information services and technology, simulations and stuff, it needs to be as easy to read and as information as possible

[MrMarriarty]

perfect i can see it now, yeah this is quite easy to achieve just using some formulas within excel, ill download it and have a tinker

[Neutrideo]

Just now, MrMarriarty said:

perfect i can see it now, yeah this is quite easy to achieve just using some formulas within excel, ill download it and have a tinker

Thanks bro

[MrMarriarty]

13 minutes ago, Neutrideo said:

Thanks bro

ok so it looks slightly differnt on mine but hay, so to be clear you want F9 to be pressed and the data in the highlighted area to be recorded then repeat over and over again??

[Neutrideo]

6 minutes ago, MrMarriarty said:

ok so it looks slightly differnt on mine but hay, so to be clear you want F9 to be pressed and the data in the highlighted area to be recorded then repeat over and over again??

If I understand you correctly, you are saying when you press f9 it will repeat it multiple times and record a average of what has shown in the highlited area.

[MrMarriarty]

15 minutes ago, Neutrideo said:

If I understand you correctly, you are saying when you press f9 it will repeat it multiple times and record a average of what has shown in the highlited area.

no what i was asking is if thats what you wanted, you would need some Visual basic to do something like that

 

Sub Auto()

Dim x

For x = 1 To 5 'how many times to loop

Sheets("Sheet1").Activate
Calculate
ActiveSheet.Range("G3").Select
ActiveSheet.Range("G3").Copy
Sheets("Results").Activate
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
    Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Next x

End

End Sub
 

[Neutrideo]

1 minute ago, MrMarriarty said:

no what i was asking is if thats what you wanted, you would need some Visual basic to do something like that

 

Sub Auto()

Dim x

For x = 1 To 5 'how many times to loop

Sheets("Sheet1").Activate
Calculate
ActiveSheet.Range("G3").Select
ActiveSheet.Range("G3").Copy
Sheets("Results").Activate
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
    Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Next x

End

End Sub
 

Which cell would I put this in

[MrMarriarty]

like so...

excel.mp4

 

this is what to put in it:

Dim x

For x = 1 To 5 'how many times to loop

Sheets("Sheet1").Activate
Calculate
ActiveSheet.Range("G3").Select
ActiveSheet.Range("G3").Copy
Sheets("Results").Activate
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
    Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Next x

End

 

Change this for the number of times to loop

For x = 1 To 5 'how many times to loop

 

[Neutrideo]

Thanks