Tidbit:CSE functions in Excel: Difference between revisions
Ttenbergen (talk | contribs) mNo edit summary |
Ttenbergen (talk | contribs) mNo edit summary |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
CSE functions (aka [https://support.microsoft.com/en-us/office/ | CSE functions (aka [https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 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). | 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). | ||
| Line 7: | Line 7: | ||
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. | 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 | 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. | ||
{| class="wikitable" | {| class="wikitable" | ||
|+ | |+ | ||
| 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. | |||
Latest revision as of 06:55, 2023 May 14
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 CONCAT(IF(B$2:B$5>"",A$1:A$5 & "; ","")) 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.