Formula wanted.

Giganews Newsgroups
Subject: Formula wanted.
Posted by:  george
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
from 1).
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
number)
B3 = 7        then      C3 = 7          (=A7 cell)
B4 = 8        then      C4 = 9          (=A9 cell because subtract the A7
number )

etc.

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.
Any idea?

Thanks.

Replies