4

Examples: Say I have several 1D arrays, such as A, B, and C:

 A    B    C
---  ---  ---
 2    4    99
 3    5    37
 4    6    42
      7

I want to use a formula to pad each of these arrays (which are named ranges) so they have the same number of rows. (Edited, to provide a more complete explanation:) I would also like to be able to shift the starting position of each array by a specified number of rows. Here is an example output, where I'm shifting A by two, B by three, and C by one position:

 A'   B'   C'
---  ---  ---
 0    0    0
 0    0    99
 2    0    37
 3    0    42
 4    4    0
 0    5    0
 0    6    0
 0    7    0

So the "formula" (really a combination of multiple existing standard formulas) to pad one of the arrays would effectively have four inputs: the original array name/range, the number of values to pad at the beginning of the original array, the number to pad at the end, and the padding value (i.e. 0, NA(), etc.).

Initial approach: My first attempt was to concatenate the three array sections (beginning padding array, original array, and ending padding array) into one array like this:

=INDIRECT("{" & IF(_NPadBegin>0, REPT(_PadVal&",", _NPadBegin-1) & _PadVal&",", "") & _ArrayName & IF(_NPadEnd>0, "," & REPT(_PadVal&",", _NPadEnd-1) & _PadVal, "") &"}")

(where the variables with underscores are named ranges for the four inputs)

However, it doesn't seem possible to build an array like this. I've searched for other methods to combine 1D arrays using a formula, but came up empty.

Another approach might be to create an array of the final desired length and initially populate it with the padding value. For example, create an A' array with eight rows of 0. (I can do this step in a dynamic formula.) Then you could overwrite the values in this initialized array with the original array, making sure to shift the original array to the correct location. For example, overwrite the 0 values in rows 2 through 4 of A' with the values of A. (I can't figure out how to do this second step...)

Background: I have multiple 1D arrays of data (generated using dynamic named ranges) that I would like to plot together on a single line chart. The arrays have differing lengths however, so I need to pad each array with a certain number of NA() values at the beginning/end of the array. They also need to be "lined up" based on an index.

It probably wouldn't be too difficult to achieve this in VBA, but I'd prefer a (non-user-defined) formula-based solution.

dnlbrky
  • 9,396
  • 2
  • 51
  • 64

1 Answers1

2

If you have values 2, 3, and 4 in A2:A4 try this formula

=IF(COUNTIF(A2:A4,ROW(INDIRECT("1:"&D2))-1),ROW(INDIRECT("1:"&D2))-1,0)

where D2 contains the number of values (8 in your example).

That will return this array

{0;0;2;3;4;0;0;0}

assumes that number series starts at zero - the zero at the end of the formula is the "padding value", change as required

OK as per comments you can do that with this array formula

=IF((ROW(INDIRECT("1:"&NPadBegin+NPadEnd+ROWS(_ArrayName)))>NPadBegin)*(ROW(INDIRECT("1:"&NPadBegin+NPadEnd+ROWS(_ArrayName)))<=ROWS(_ArrayName)+NPadBegin),N(OFFSET(_ArrayName,ROW(INDIRECT("1:"&NPadBegin+NPadEnd+ROWS(_ArrayName)))-1-NPadBegin,0,1)),PadVal)

assumes that _ArrayName is a vertical 1D array

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • Thanks Barry. As I understand your solution, it assumes that A2:A4 (aka _ArrayName) is actually the index of the array. So if I had array C that was `{99;37;42}`, this solution would not work. I attempted to extend your solution, but got stuck when trying to insert `ROW(INDIRECT("1:"&ROWS(_ArrayName)))+_NPadBegin-1` where you have `A2:A4` in the initial `COUNTIF`. It is giving me `#VALUE!` errors. – dnlbrky Dec 05 '12 at 22:23
  • OK, I'm probably not quite understanding what you want. If {99;37;42} is the base array then what result do you expect? I assumed you would want an array of values which are all zeroes except for those three (99,37 and 42) in the correct positions - but that only makes sense in my solution if D2 (size of the array) is 100 or higher. – barry houdini Dec 05 '12 at 22:53
  • For the inputs _ArrayName = {99;37;42}, _NPadBegin = 2, _NPadEnd = 3, and PadVal = 0, I expect the output to be {0;0;99;37;42;0;0;0}. – dnlbrky Dec 05 '12 at 23:00
  • Gotcha! that's not easy to do with a formula, as you found out - VBA might be simpler (although I can't tell you how to do it in VBA!), but I edited my Answer to show a formula method. – barry houdini Dec 05 '12 at 23:45
  • It looks like the updated solution does exactly what I was wanting. I'll test it a little more tomorrow and also see if I can (attempt to) understand how it works, and then mark the answer. Thanks! – dnlbrky Dec 06 '12 at 05:29
  • Yep, it works! Here is an alternate version where you can specify the final length of the array (_NFinal) like Barry initially suggested rather than the number of pads to add at the end (_NPadEnd), which makes it a little simpler: `=IF((ROW(INDIRECT("1:"&_NFinal))>_NPadBegin)*(ROW(INDIRECT("1:"&_NFinal))<=ROWS(_ArrayName)+_NPadBegin),N(OFFSET(_ArrayName,ROW(INDIRECT("1:"&_NFinal))-1-_NPadBegin,0,1)),_PadVal)` – dnlbrky Dec 06 '12 at 15:12
  • I'm having issues with the OFFSET when _ArrayName contains a function like TREND. I've posted this separately in another question. [link]http://stackoverflow.com/questions/13770919/excel-offset-function-error-when-using-trend-in-reference – dnlbrky Dec 07 '12 at 21:08