The recent updates to the ConBeamU spreadsheet contained two new functions for entering and re-sizing array functions, and since they will be useful on any spreadsheet containing array functions (which here means almost all of them) I thought they deserved a blog post of their own.
I have added the new functions to the CSplineA spreadsheet, which can be downloaded from:
The code for the new functions was taken from: Technicana . The only changes I have made are:
- If the original function failed for any reason the array function being re-sized was deleted. I have added a couple of lines so it will be re-written back to the spreadsheet.
- I have changed the shot-cut codes as detailed below.
To use the functions start by entering any function that returns an array in the top-left corner of the desired output range:
To expand the function to display the full extent of the array, press Ctrl-Shift-S:
To re-size the array select the required range and press Ctrl-Shift-R:
To move the array to a range overlapping with the original range (with the new range to the right and/or below the original), select the top left cell of the new range then press Ctrl-Shift-S (for the full array), or select the output range required and press Ctrl-Shift-R:
If the destination range is either to the left of, or entirely outside, the original range then the array function must first be copied to the top-left cell of the destination range, then proceed as above.
A word of warning: the functions clear the undo stack, and will write over any data in the output range without warning, so if there is any danger of the output range writing over any data you need, save your work before proceeding.