| A | B | C | D | E | F | G | H | I | ||
|---|---|---|---|---|---|---|---|---|---|---|
1 | Copyright: Cheat Sheets LLC (CheatSheets.blog) | |||||||||
2 | ||||||||||
3 | ID | Title | Blog Post | Example 1 | Example 2 | Description | Formula | |||
4 | F01 | Assign to Tiers or Cohorts | Link | Link | Link | Use a reference list (e.g., if >= X & < Y then tier Z) to arrange individual entities into tiers, cohorts, segments etc. | ARRAYFORMULA ( FILTER ( returnRange , value >= min_refRange , value < max_refRange ) ) | |||
5 | F02 | Filter with "IN" Critieria | Link | Link | -- | Filter a range to include those which match any from a list of criteria (mimics "IN" SQL function) | FILTER ( returnRange , ISNUMBER ( MATCH ( criteriaRange , inclusionList , 0 ) ) ) | |||
6 | F03 | Filter with "NOT IN" Critieria | Link | Link | -- | Filter a range to exclude those which match any from a list of criteria (mimics "NOT IN" SQL function) | FILTER ( returnRange , NOT ( ISNUMBER ( MATCH ( criteriaRange , inclusionList , 0 ) ) ) ) | |||
7 | F04 | Return X Sorted by Y | Link | Link | Link | Return a filtered single-column data set sorted by data in a different column | TRANSPOSE ( INDEX ( TRANSPOSE ( SORT ( multicolumnRange , sortColumn_number , TRUE ) ) , returnColumn_number ) ) | |||
8 | F05 | Unique List Across Multiple Columns | -- | Link | Link | Return a unique list of values from a multi-column range | SORT ( UNIQUE ( TRANSPOSE ( SPLIT ( CONCATENATE ( ARRAYFORMULA ( multicolumnRange & "|" ) ) , "|" ) ) ) , 1 , TRUE ) | |||
9 | F06 | Unique List Across Multiple Columns (XL) | -- | Link | -- | Return a unique list of values from a multi-column range. The same purpose as F06 (above). Use this if you get the "50K character limit" error | ARRAYFORMULA ( UNIQUE ( TRANSPOSE ( SPLIT ( CONCATENATE ( targetColumn_1 & "|" , targetColumn_2 & "|" ) , "|" ) ) ) ) | |||
10 | F07 | Avoid COUNTA False Positive with IFERROR | Link | -- | Link | If you wrap FILTER in COUNTA to count filtered text values it will never read "0" because it registers "#N/A" as a value. Fix that with IFERROR | COUNTA ( IFERROR ( FILTER ( returnRange , criteriaRange = criteria ) ) ) | |||
11 | F08 | Filter with "OR" Clause | Link | -- | Link | By default, FILTER uses "and" logic (filter if this is true AND this is true). This approach let's you use "OR" logic | FILTER ( returnRange , ( criteriaRange_1 = criteriaRange_1 ) + ( criteriaRange_2 = criteriaRange_2 ) + ( criteriaRange_3 = criteriaRange_3 ) ) | |||
12 | F09 | Add a Month with EDATE | Link | -- | Link | Without EDATE, adding a month is surprisingly complex. This formulas solves for that | EDATE ( value , addMonths_count ) | |||
13 | F10 | Trim Unwanted Text with Substitute | Link | -- | Link | The "TRIM" formula removes spaces. But what if you need to remove underscores, commas or asterisks? That's what this formula is for | SUBSTITUTE ( value , removedString , "" ) | |||
14 | F11 | Format Titles with TEXT | Link | -- | Link | Here's a link to the TEXT syntax guide | TEXT ( value , syntax ) | |||
15 | F12 | Filter with Substring Criteria | -- | Link | -- | For example, search an array and return every value that contains and underscore, or the word "renewal" | FILTER ( returnRange , ISNUMBER ( SEARCH ( substring , substringRange ) ) | |||
16 | F13 | Determine if Substring in Range | -- | Link | -- | This is basically "ctrl+F" in the form of a formula. Works across multple ranges | IFERROR ( IF ( SEARCH ( searchString , CONCATENATE ( searchRange_1 , searchRange_2 , searchRange_3 ) ) > 0 , "YES" ) , "NO" ) | |||
17 | F14 | Replace Values within Filtered Return | -- | Link | -- | For example, search an array and, for any returned values, replace any underscores with commas | FILTER ( ARRAYFORMULA ( SUBSTITUTE ( returnRange , removedString , replacementString ) ) , criteriaRange_1 = criteria_1 ) | |||
18 | F15 | Gantt Chart | Link | Link | -- | Create an in-cell Gantt chart! | SPARKLINE ( { INT( start_date ) - INT( overall_start_date ) , INT ( end_date ) - INT( start_date ) } , { "charttype" , "bar" ; "color1" , "white" ; "color2" , "black" ; "max" , INT ( overall_end_date ) - INT ( overall_start_date ) } ) | |||
19 | F16 | Find Column Letter of Searched Value | -- | -- | Link | Search for a value in a range and return the column letter | SUBSTITUTE ( ADDRESS ( 1 , MATCH ( searchValue , entireRowRange , 0 ) , 4 ) , 1 , "" ) | |||
20 | F17 | Find Row Number of Searched Value | -- | -- | Link | Search for a value in a range and return the row number | SUBSTITUTE ( ADDRESS ( MATCH ( searchValue , entireColumnRange , 0 ) , 1 , 4 ) , "A" , "" ) | |||
21 | F18 | Compound Growth Rate (CxGR) | -- | -- | Link | Calculate the growth rate required to arrive at the desired end balance over a set number of periods | ( endBalance / startBalance ) ^ ( 1 / periodCount) - 1 | |||
22 | F19 | Compound Monthly Growth Rate (CMGR) | -- | -- | Link | Calculate the monthly growth rate required to arrive at the year-end balance in a single year | ( endBalance / startBalance ) ^ ( 1 / 12 ) - 1 | |||
23 | F20 | $K/$M/$B Custom Number Format | -- | -- | -- | Shorten currency formatting using this customer number format. From the navigation bar, go Format > Number > Custom Number Format and then paste this into the field at top | [<999950]$0.0,"K";[<999950000]$0.0,,"M";$0.0,,,"B" | |||
24 |