Function Reference: BYCOL, BYROW
| Syntax | ||
| =BYCOL(array,function) =BYROW(array,function) |
||
| Purpose / Description | ||
| Returns an array in which is the result of calling a function to process each of the columns (BYCOL) or rows (BYROW) in a given array. Accepts calls to LAMBDA formulas or pre-defined custom functions defined by LAMBDA. | ||
| Arguments | ||
| Name | Type | Description |
| array | reference or array of any | A reference or nested expression that returns a valid reference, a constant array of any values or a nested expression that returns an array of any values. |
| [function] | function name or nested function | The name of a custom function defined using LAMBDA, or a LAMBDA nested with its argument definitions and embedded calculation. The custom function or the nested LAMBDA needs to accept a single range or array argument. The BYCOL and BYROW call this function passing each column or row respectively to that function and each collates and returns the results. |
| Return Values | ||
| Type | Specific Value | Description |
| array of any | Regardless of whether the original array is a reference to a range, a calculated array, or a constant array, the result returned is an array containing the results from the embedded function. By default, BYCOL will return a horizontal array, being the respective answers for each column, and BYROW will return a vertical array, being the results from each row. | |
| error | #VALUE! | If the wrong number of arguments is defined for the nested function. or the wrong number of arguments is passed to function. |
| error | #CALC! | If the lambda returns an array of values. The function called must be designed to return a single result only, per column. |
| error | #N/A | No function is passed to the second argument function. |
| error | #SPILL! | If the number of cells required is more than the available cells. |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Comment: | These are essential additions to Excel, allowing for the derivation of column and row totals in processing arrays and blocks of values in dynamic arrays. | |
| Comment: | This Wiki includes a library of LAMBDAs that may be useful in removing the drudgery of filling workbooks with nested LAMBDAs to handle row and column totals. But as demonstrated in the linked sample workbook, the called LAMBDA can be pre-prepared and does not need to be repeated in every BYCOL and BYROW function call. | |
| Examples: | Open / download sample workbook: BYCOL, BYROW.xlsx | |