0

my problem is:

I have a matrix in excel then a function (repeated N times) that extract from this matrix a random number. This happen everytime i hit F9 or everytime something change in the sheet. After this i have another cell with a sum of all then numbers extracted. I would like to keep track of the change of this last cell in a linear chart to see how it goes on.

Thank you so much in advance

rikymiami
  • 71
  • 2
  • 4
  • 14
  • search for macro (vba code) which will use `Calculate Event` of Worksheet to track cells changes. Macro Recorder doesn't support events therefore it's not a good starting point in your situation. – Kazimierz Jawor Oct 09 '13 at 12:03
  • Why don't you store the values in an hidden sheet and then you generate the graph using it as source? – mucio Oct 09 '13 at 12:03
  • mucio i would love, how can i do it? (i mean the record the values in another sheet? – rikymiami Oct 09 '13 at 12:12
  • You can extract a random sample with either a worksheet formula, or a UDF, or a macro. If you use a macro, the macro can store the sequential selections in a column, say column **Z**. Then you can always compare the **SUM(Z1:Z99)** with **SUM(Z1:Z100)**. – Gary's Student Oct 09 '13 at 12:17

1 Answers1

0

Solution 1: formulas

if you want to repeat T times, just instead of only 1 series of N number create a matrix of N by T. Then ad 1 last row (or column) under (or next to) the N functions for all T which contains the sum.

Now relate a chart to this last row (or column).

Solution 2: VBA

Create a piece of code that loops T times and copies the value of the cell with the sum into the next empty cell in a designate part of your excel workbook.

    Sub aaa
      dim i as Long
      dim T as Long

      T = 1000

      For i = 1 to T
        Range("rSum").Copy
        Cells(rows.count,1).offset(xlUp)).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

      Next i
    End Sub
K_B
  • 3,668
  • 1
  • 19
  • 29
  • The problem with your suggestion is insuring that the values in A1, A2, A3, remain stable when A4 is added. – Gary's Student Oct 09 '13 at 12:27
  • i already did all what u say. right now i have a cell with a sum =SUM(C30:C32;B30:B58;A30:A51) and i need to keep track of the change in this cell, if i do your way the number in each new cell under the first will grow....i only need to record the change! – rikymiami Oct 09 '13 at 12:27
  • Sorry I initially misread your question completely, I now corrected my answer(s) – K_B Oct 09 '13 at 12:42