Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? . |
#3
|
|||
|
|||
|
#4
|
|||
|
|||
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... |
#5
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
Array Manipulation | Excel Worksheet Functions | |||
Array Manipulation | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |