Array Manipulation
 

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 a

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?

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

-----Original Message-----
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 a

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?

.

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...

Marston

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

If Rng is in a column (e.g. D1:D5):
=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 D1:D5 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(Rn g))))=
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.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

skrev i en meddelelse
oups.com...
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...