How to calculate the percentual difference of speeds between 2 consecutive runs or 2 consecutive hikes, in the Numbers.app?

Below is another way.

In a new column, say column N, put the formula =ROW() in every data row (not needed in any headers). This will give every row its row number.

A formula to get the percentage increase in "hike" or "run" speed from column K would be

=K/INDEX(K,MAXIFS(N,C,C,N,"<"&N))-1

The MAXIFS part of the formula returns the maximum row number where column C has the same value as the current row (i.e,, "hike" or "run") and the row number in column N is less than the current row number. INDEX then gets the speed from column K in that row.

For upper rows where there is no earlier "hike" or "run", MAXIFS will return 0. INDEX(K,0) apparently gives the current row's value so the result of the formula will be a 0% increase.

For lower rows where there is no data, the formula will give an error (divide by 0 error). You can put IFERROR around the formula or handle it some other way.

These formulas will allow sorting, manual rearranging of rows, and deletion or addition of rows.

Create two new columns to count the runs and hikes. In my example they are O and P with headers Run and Hike, it's important they match the texts you enter in column C.

Enter in O2, then drag-copy to P2:

=IF(COUNTMATCHES($C2,O$1)>0,COUNTMATCHES(TEXTJOIN(",",TRUE,OFFSET($C$1,1,0,ROW()−1,1)),O$1),"")

Enter in M2:

=IFERROR(IF(ISBLANK('Date/Time'),"",IF(O2≠"",K2÷XLOOKUP(O2−1,run,'Km/h',0,0)−1,K2÷XLOOKUP(P2−1,Hike,'Km/h',0,0)−1)),"")

Then copy down those three formulas in their respective columns.

Explanations:

The count of runs and hikes is established by concatenating the content of the C column from row 2 to the row of the formula and counting how many times the column header appears in the concatenated text.

XLOOKUP then looks for that value minus 1 to establish the row of the previous similar event, from which it extracts the speed which is compared to the speed on the current row.

It does not matter that the first data row is the third row and the first number is 3.

You need the column of ROW() formulas so that MAXIFS has something to find the maximum of. It looks in that column for the maximum number that is less than the one in its own row and where the value in column C is the same as in its own row. You can hide the column of row numbers after you have everything set up and working.

There are other ways to approach the problem that that do not require the extra column but they have other drawbacks. They either require OFFSET (or other function like that) or they are not sortable and rows cannot be moved, added, or deleted without redoing the column of formulas.

Here is an OFFSET version, starting at row 4:

M4 =C4/XLOOKUP(C4,OFFSET(C$1,2,0,ROW()−3,1),OFFSET(K$1,2,0,ROW()−3,1),K4,0,−1)-1

This will work in all rows except row 3. For that row (the first of your data rows), you can replace it with 0%. If you add or remove a header row, the formula needs to be edited. I think the column of row numbers makes for a simpler, easier to understand formula that is less prone to errors/bugs but some would rather not have extra columns. If you will never insert rows between other rows or delete rows or sort the table or move rows around manually, you can use a much simpler formula but you might find you forgot to make an entry in the table and you will not be able to insert a row for it.

M4 =XLOOKUP(C4,C$3:C3,K$3:K3,0,0,−1)

The comma is there on purpose, it's the separator for all joined values, the first argument of the TEXTJOIN function.

I wonder why you say that the formula would have to be adapted for the first rows (except changing the 2's for 3's since you have two header rows). Did you change the format of the rows at one point?

Anyway, kudos to Badunit for an amazing formula. So simple and elegant. However I still can't wrap my head around the fact that a range can be used for the MAXIFS criterias (third and fifth parameters). It works (as do also single cells on the row of the formula), so given a range Numbers will just take the cell on the current row! A non-documented trick to remember, for sure.

However I still can't wrap my head around the fact that a range can be used for the MAXIFS criterias (third and fifth parameters). It works (as do also single cells on the row of the formula), so given a range Numbers will just take the cell on the current row! A non-documented trick to remember, for sure.

I'm sure it is documented somewhere, or at least it used to be. If a function requires a singular value, it will interpret it as a single cell in the current row (or in the current column if the reference was to a row like 3:3). If it is a range parameter, it will be interpreted as a range. I don't know if it is ever necessary to use it like this but it is a neat way to write a formula that can be pasted into all rows without change. One thing it does let you do is pick up the cell from the referenced column and drag it elsewhere without the reference following it around. Not that that matters here but it is one feature of it.

Bad Company is a great band and I listened to them a lot back in the day but "bad unit" was something a friend of mine said back in the 80's when talking about a broken piece of equipment. It might have been from a line in the movie Short Circuit but I don't know for sure. I used it as a handle for games way back then and for other things along the way.