Tidbit:CSE functions in Excel: Difference between revisions
Ttenbergen (talk | contribs) m link fix |
Ttenbergen (talk | contribs) mNo edit summary |
||
| Line 34: | Line 34: | ||
|y | |y | ||
|} | |} | ||
I tried these out in LibreOffice Calc rather than Excel. They work there as well. One strange thing I found is that CSE doesn't always seem to "catch" when leaving a cell. I found that removing the "=" you usually need to add to designate a function often fixes it. | |||
Revision as of 17:52, 2023 February 18
CSE functions (aka array formulas) in Excel are functions that apply across a range of cells to aggregate the result in once cell.
CSE functions are applied when a function is entered into the cell, and instead of leaving the cell pressing the enter key, it is left by pressing ctrl-shift-enter (hence the CSE name).
The functions are useful for generating lists of values in a single cell.
For example, a CSE function could be used to generate a semicolon separated list of email addresses based on a column of email addresses and a second column that is used as the filter.
This table contains the 4 bottom rows of data. Entering the formula <code>CONCAT(IF(B$2:B$5>"",A$1:A$5 & "; ","")) </code> into the top right cell and then clicking ctrl-shift-enter will populate the cell as seen here.
| A | B | |
| 1 | a@b.ca; d@b.ca; | |
| 2 | a@b.ca | x |
| 3 | b@b.ca | |
| 4 | c@b.ca | |
| 5 | d@b.ca | y |
I tried these out in LibreOffice Calc rather than Excel. They work there as well. One strange thing I found is that CSE doesn't always seem to "catch" when leaving a cell. I found that removing the "=" you usually need to add to designate a function often fixes it.