Array Manipulation

[marston_gould]

Does anyone have a method to do the following with arrays:

1) I have 2 series of 1 x n values
2) I difference the 2 series using arrays
(e.g. In cell C1 : {=A1:A10 - B1:B10}

So let's assume that A1 through A10 hold the following values
and B1 through B10 hold the second set of values - then the resulting
array values are:

10 1 = 9
9 2 = 7
8 1 = 7
7 3 = 4
6 1 = 5
5 1 = 4
4 2 = 2
3 1 = 2
2 2 = 0
1 0 = 1

Now what I'd like to do is create a new array that has only a subset of
this original. In particular, what I'd like is an
array that has all the values but the first.

Right now I have the entire array in a single cell and I'd like to keep
it that way to reduce real estate on the spreadsheet - thoughts?

 

[Jason Morin]

Not sure what your final objective is. Maybe...

=IF(ROW(A1:A10)>1,A1:A10-B1:B10)

This will give you:

{FALSE;7;7;4;5;4;2;2;0;1}

HTH
Jason
Atlanta, GA

 

[Alan Beban]

Does anyone have a method to do the following with arrays:

1) I have 2 series of 1 x n values
2) I difference the 2 series using arrays
(e.g. In cell C1 : {=A1:A10 - B1:B10}

So let's assume that A1 through A10 hold the following values
and B1 through B10 hold the second set of values - then the resulting
array values are:

10 1 = 9
9 2 = 7
8 1 = 7
7 3 = 4
6 1 = 5
5 1 = 4
4 2 = 2
3 1 = 2
2 2 = 0
1 0 = 1

Now what I'd like to do is create a new array that has only a subset of
this original. In particular, what I'd like is an
array that has all the values but the first.

Right now I have the entire array in a single cell and I'd like to keep
it that way to reduce real estate on the spreadsheet - thoughts?

Can you post the code that gets the result into 1 cell?

Alan Beban

 

[marston_gould]

Thanks for all the interest....

Let me start off by saying that whatever method I use, not that this is
the preferred method, but I have been requested to
come up with a solution that does NOT use VB.

High level - what I'm trying to understand is whether or not there are
easy ways to manipulate array data in single cells without having to
give real estate to the entire array on a worksheet. I know that some
of the basic functions that I'll need can be achieved (such as
addition, subtraction, etc.) But what I'm unsure of is whether I can
handle some of the more complicated processes I need to accomplish.

I'm trying to use arrays to manage, in essence, information about a
queue.
The queue values represent # of items, people, etc.
E.g.
Time 1: in order of spot: 6 people, 10 people, 9 people, etc.

As I march to the next time period - some of the items/people in spot 1
can leave, some don't
E.g.
Time 1+t: 3, 10, 9

AND everyone below the first spot marches up one more into the queue

Time 1+t = 3+10 = 13, 10-10+9 = 9, etc.

I know that first operation (removing those from the first spot is
easy)
But taking a portion of the array (the 10,9, etc.) and repositioning it
is the difficult part...

 

[Leo Heuser]

Marston

If I have understood you correctly, this array formula
will do the job.

If Rng is in a column (e.g. D15):
=MMULT(TRANSPOSE(Rng),(TRANSPOSE(ROW(INDIRECT("1:"&
ROWS(Rng))))=SMALL(ABS(ROW(INDIRECT("1:"&ROWS(Rng)+1))-2),
ROW(INDIRECT("2:"&ROWS(Rng)+1))))+0)

If D15 contains e.g. 4,5,6,7,8, the formula will return the array
{9,6,7,8,0}

If Rng is in a row (e.g. B2:L2):
=MMULT(Rng,(TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(Rng))))=
SMALL(ABS(ROW(INDIRECT("1:"&COLUMNS(Rng)+1))-2),
ROW(INDIRECT("2:"&COLUMNS(Rng)+1))))+0)

Both formulae must be entered with <Shift><Ctrl><Enter>, also if
edited later.