Sometimes, you may need to identify the start and end points of a list of numbers or dates in Google Sheets. This can be helpful for spotting missing values in the sequence. Luckily, there is a simple formula that can do this for you.
The Start and End Points Formula
To find the start and end points from a sequence of numbers or dates in Google Sheets, you can use the MIN and MAX functions combined with a dash. Here’s how it works:
=MIN(range)&"-"&MAX(range)For example, let’s consider the list {5, 6, 7, 35, 36, 39, 40, 41, 42, 43}. The start and end points will be the ranges 5-7, 35-36, and 39-43.
An Array Formula for Complex Lists
However, when there are missing numbers in the sequence, there will be multiple start and end points. This is because the gaps created by missing numbers result in multiple ranges.
To generate these ranges, you may need to use a more complex formula. Here’s an array formula that can help you accomplish this:
=ARRAYFORMULA( LET( list, E2:E, seq, SPLIT(FLATTEN(SPLIT(TEXTJOIN("|",TRUE,IFNA( XLOOKUP( SEQUENCE(MAX(list)-MIN(list)+1,1,MIN(list)), list, list ),",")),",")),"|"), fltr, FILTER(seq,CHOOSECOLS(seq,1)<>"|"), BYROW(fltr,LAMBDA(r, TEXTJOIN("-",TRUE,TO_DATE(MIN(r)),IF(MAX(r)=MIN(r),,TO_DATE(MAX(r))))) ) ) )Please note that you need to remove the TO_DATE functions from the formula if you’re using it with a numeric list.
Understanding the Formula
There are four key steps involved in the above formula:
Step 1: XLOOKUP to Remove Missing Values
The first step is to use an XLOOKUP array formula to remove the missing values from the sequence.
=ARRAYFORMULA(XLOOKUP(SEQUENCE(MAX(F2:F)-MIN(F2:F)+1,1,MIN(F2:F)),F2:F,F2:F))This formula matches the search keys in the list with the values in the list itself, effectively removing any missing values.
Step 2: Combining and Formatting the Values
Next, we combine the values and format them using the TEXTJOIN function.
=ARRAYFORMULA(TEXTJOIN("|",TRUE,IFNA(H2:H,",")))This formula replaces the missing values with commas and joins all the values together using a pipe delimiter.
Step 3: Splitting and Flattening
In this step, we split the combined values twice and then flatten them.
=ARRAYFORMULA(SPLIT(FLATTEN(SPLIT(I2,",")),"|"))This allows us to separate the values and create a single column of start and end points.
Step 4: Filtering the Results
Next, we filter out the rows that contain a pipe (|) in the first column.
=FILTER(I3:N9,I3:I9<>"|")This ensures that we only have the valid start and end points in our result.
Step 5: Extracting the Start and End Points
Finally, we extract the minimum and maximum values from each row and join them with a dash.
=BYROW(I11:N12,LAMBDA(r,TEXTJOIN("-",TRUE,MIN(r),MAX(r))))This formula gives us the start and end points from the list of dates or numbers.
Aggregate Data Based on the Start and End Points
Apart from identifying missing sequence values, you can also use the start and end points to summarize data within the ranges. Let’s explore two examples using the SUMIF and COUNTIF functions.
Example: Summing Values
Suppose we have a list of purchase dates, items, and quantities in columns A, B, and C. To sum the quantities falling within each range, use the following formula:
=SUMIF(ISBETWEEN($A$2:$A,CHOOSECOLS(SPLIT(E2,"-"),1),CHOOSECOLS(SPLIT(E2,"-"),2)), TRUE, $C$2:$C)This formula checks if the dates in column A fall within the range specified in cell E2. If they do, it sums the corresponding quantities from column C.
Example: Counting Values
To count the values corresponding to the start and end points, you can use the COUNTIF function. Here’s an example formula:
=COUNTIF(ISBETWEEN($A$2:$A,CHOOSECOLS(SPLIT(E2,"-"),1),CHOOSECOLS(SPLIT(E2,"-"),2)), TRUE)This formula counts the number of dates in column A that fall within the range specified in cell E2.
Now you have a powerful formula to find start and end points in Google Sheets and even aggregate data based on those points. Happy analyzing!
Learn more about Google Sheets and other useful tips on Crawlan.com
