0

I'm trying to create a sheet that acts as a model stock market. I have code to randomly generate a positive or negative percentage to apply to a set(and later varying) principal. Every time I manually calculate my sheet, I need a new cell to generate with the latest balance(principal*random%) while still showing the previous balances. I've tried testing if the cell below is empty and then placing text in it, but once it reads the cell as no longer empty it becomes a circular reference. Ideal I'd like to be able to 1) calculate the new balance after applying the first percentage 2) save the new balance in its own cell 3) read that cell as the new balance while still viewing the initial value 4) repeat the function every time I calculate the page 5) create a graph and later algorithm plotting the points to resemble market trends.

Jorlack
  • 1
  • 1
    A formula calculates a value, so the value is a calculation, which changes if anything related to the calculation changes. You need VBA to store calculated values – P.b Aug 30 '22 at 06:54

1 Answers1

0

You could use the Workbook_Change() event in VBA and write a short copy and paste script in there to store results of your calculation. You would probably want to combine this with a vba command for finding the last row in your results column to paste the new value.

Dattel Klauber
  • 800
  • 1
  • 2
  • 17