google-sheets
Create rows with intervals of 50
You have 3 columns: START, FINISH and SPEED, like this:
| START | FINISH | SPEED |
|---|---|---|
| 0 | 123 | 20 |
| 123 | 363 | 60 |
The objective is to create a third column called intervals, that separates the intervals of 50 meters between start and finish mantaining the speed indicated, for this example, the output columns should look like this:
| INTERVAL | SPEED |
|---|---|
| 0 | 20 |
| 50 | 20 |
| 100 | 20 |
| 150 | 20 |
| 200 | 60 |
| 250 | 60 |
| 300 | 60 |
| 350 | 60 |
| 400 | 60 |
I've tried formulas like these:
=ARRAYFORMULA(FLATTEN(IF(A2:A<>""; A2:A + SEQUENCE(1 + (B2:B - A2:A) / 50; 1; 0; 50); )))
But I'm struggling to generate the column completely.
Any help would be appreciated.
Solution
You can use this formula. The first line creates the 50s interval, and the second one maps those numbers and looks for them in the corresponding column with XLOOKUP:
=LET(nums,SEQUENCE(ROUNDUP(MAX(B:B)/50)+1,1,0,50),
MAP(nums,LAMBDA(num,HSTACK(num,XLOOKUP(num,A:A,C:C,,-1)))))
