560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Fourteen new functions coming to Excel
Part three of a three-part series of blogs
Get a preview of the 3 text functions and 11 array functions coming to your version of Excel very soon!
There are 11 new functions in Excel for manipulating arrays, all illustrated using the current state (at the time of writing) of the Northwest Counties Division 5 Central South squash league:
The range A1:F15 shown has been given the name League.
You can use CHOOSEROWS to pick out only certain rows:
You can specify one or more rows to pick out. This function will show the first row (the title row), and rows 4 and 5.
Or CHOOSECOLS to pick out only certain columns:
This would give the second and third column of the range only.
You can nest these functions to get exactly what you want:
This formula would pick out rows 1, 4 and 5, and columns 2 and 3.
You can use the TAKE or DROP function to do this. Here's an example of choosing rows/columns to keep:
This function picks out the first 5 rows and 3 columns from the block of data (if you omit the number of columns you would get all of them).
You could achieve the same effect by dropping the last 10 rows and last 4 columns:
If you put negative numbers in, Excel will start dropping from the bottom/right rather than the default top/left.
If you want to make ranges bigger you can expand them to fill a certain range (optionally specifying what to use as padding). Here's an example:
This formula takes an array which is 3 rows high and 2 columns wide, and expands it to fill an array 4 rows high by 4 columns wide, putting XXX (rather than the default #N/A) in any unused cells.
The number of rows and columns must each be at least as big as the respective number of rows and columns in the original range.
This is sometimes called broadcasting. You can flatten arrays to a single row or single column using TOROW or TOCOL:
The first formula collapses the mini-league to a single row; the second to a single column.
Both TOROW and TOCOL take three arguments, of which only the first is compulsory:
|1||array||Compulsory||The block of cells you're collapsing into one dimension.|
|2||ignore||Optional||Whether to ignore certain types of values (see table below - by default Excel includes all values).|
|3||scan_by_column||Optional||Whether to scan across and then down (the default, as above) or down and then across.|
The possible things you can ignore are as follows:
|0||Include everything (the default)|
|1||Ignore blank cells|
|3||Ignore blank cells and errors|
The opposite of collapsing down to a single row/column is to expand a single row or column into a two-dimensional array using WRAPROWS or WRAPCOLS:
This example creates as many rows as are needed to accommodate the 5 films in the given range, subject to having a maximum of 3 values in each row.
Both functions take up to three arguments:
|1||vector||Compulsory||The range (normally row or column) of cells you want to reshape.|
|2||wrap_count||Compulsory||The maximum number of values you can have in each row (WRAPROWS) or column (WRAPCOLS).|
|3||pad_with||Optional||What to put in any empty cells (this defaults to #N/A).|
You can join ranges together vertically:
This formula combines the two ranges, one underneath the other. Because they're not the same width, any blank cells are filled with #N/A.
Exactly the same formula, but stacking across rather than down.
|Parts of this blog|
Some other pages relevant to the above blogs include:
25 Aytoun Street