Tidbit:CSE functions in Excel: Difference between revisions
Ttenbergen (talk | contribs) Created page with "CSE functions (aka [https://support.microsoft.com/en-us/office/video-array-formulas-8ff8257a-b28e-4e81-b4f8-30f793412dfa 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 va..." |
Ttenbergen (talk | contribs) mNo edit summary |
||
| (3 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 5: | Line 5: | ||
The functions are useful for generating lists of values in a single cell. | 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. | 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. | |||
{| class="wikitable" | |||
|+ | |||
| | |||
|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. | |||
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.