|Date:||Tue, 31 Mar 2009|
Hi to everyone.
I need help to find a formula in a slightly complicated problem.
I have three successive columns in Excel A, B and C.
In A column I have A1:An = 1,2,3,â€¦â€¦n (successive positive integers starting
In B column B1:Bn I apply the Randbetween() function in the following way
(Lets say that n = 100): B1 = randbetween(1;100), B2 = randbetween(1;99),
B3=randbetween(1;98)â€¦â€¦â€¦.B99 = ranbetween(1;2) and B100 =1
I would like somehow in C column, using the extracted numbers in B column,
to result the corresponding number of A column, but subtracting every time
the previous extracted numbers of A column in the above calculated rows. That
means for example that a hypothetical solution (A1:A100 =1â€¦.100) for the
first 4 rows could be:
B1 = 97 then C1 = 97 (=A97 cell)
B2 = 97 then C2 = 98 (=A98 cell, because subtract the A97
B3 = 7 then C3 = 7 (=A7 cell)
B4 = 8 then C4 = 9 (=A9 cell because subtract the A7
Here is a full a simple example for n = 5 (1,2,3,4, and 5)
A1 B1 C1
(1..5) (randbetween(1â€¦..5)) (â€¦....?.....)
1 5 5
2 3 3
3 3 4
4 2 2
5 1 1
(It seems like having a column of bricks one on the top of the others and
every time we subtract randomly one from the middle, the above bricks move
down to fill the empty space).
I hope you understand what I want to do.