How can I find the amount of change in a range of numbers?

Hi dchandler95,

First, let's use another table to find the column numbers containing scores (non-blank columns). You can hide the extra table (cut and paste to another Sheet) when all is working.

Formula in "Non blank column numbers" B2 is IF(ISBLANK(Table 1::B2),"",COLUMN())

Fill down and fill right.

In Table 1, formulas are

L2 is ROW()

M2 is MIN(Non blank column numbers::2:2)

N2 is MAX(Non blank column numbers::2:2)

O2 is INDIRECT(ADDRESS(L2,M2,4)

P2 is INDIRECT(ADDRESS(L2,N2,4)

Q2 is P2−O2

The red error triangles are due to no scores in those rows.

Hide columns L to P

Regards,

Ian .

N2 =XLOOKUP(O2,B$1:M$1,B2:M2)−XLOOKUP(P2,B$1:M$1,B2:M2)

O2 =MAX(B$1,MAXIFS(B$1:M$1,B2:M2,"<>"))

P2 =MAX(B$1,MAXIFS(B$1:M$1,B2:M2,"",B$1:M$1,"<"&O2)+1)

fill down

The years in row 1 cannot be text. Number or date format will work.

Formula in O is looking for the max year that has an entry. If no columns have an entry then it defaults to the earliest year (column B, 2014). (MAXIFS will be 0 when it finds no match)

Formula in P is looking for the max year that is less than the year in O and has no entry in it (is blank). The cell to the right is the one we want so it adds 1 to the result. If there are no blank cells to the left of O's year, it defaults to the earliest year (column B, 2014).

Formula in N looks up the data for the two years and subtracts.

Hide columns O and P.

Assuming your data starts in column B (which would be 2014), the formula in cell N2 could be

=XLOOKUP(REGEX("."),B2:M2,B2:M2,0,2,−1)−XLOOKUP(REGEX("."),B2:M2,B2:M2,0,2,1)

Scratch that. I misunderstood what you meant by "range". The formula I gave subtracts the leftmost value from the rightmost.

-----