Formula wanted.

[George]

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.

 

[dranon]

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?

I think you will need VBA. It is a simple matter to determine how
many times in Column B the new value has been previously selected, so
you can subtract 1 from the number (in order to "remove" the lower
brick). That would be in cell C2:

=B2-COUNTIF($C$1:C1,"="&CELL("CONTENTS",B2))

But the problem comes when the resulting brick you want to remove has
already been removed. You somehow have to keep track of those, too,
and that sounds like iteration. Otherwise, if the 20th value is "10"
and you count the fact that "10" has been "removed" twice, you would
then want to remove the brick number "8", but in the first 19 rows, 8
might already have been removed.

I suppose there is a limit to the expected iterations and you could
craft a deep enough nested formula. But that would be FUGLY.