Difference between last two cells with offset functionality

[cazfromoz]

Hello everyone,
In the variance column I would like a dynamic formula that finds the difference between the current month for example Nov 21 and the difference between the previous month Oct 21. I have been manually inputting =g4-f4 and dragging the formula down for the variance column each month. I have tinkered with an offset formula - =(OFFSET($B$17,0,COUNT(C17:N17),1,-2)) but I don't know how to include the subtraction component. Works perfectly if I wanted to SUM the cells, but I need to find the difference.
Thank you.

Invoice Description

Jul-21

Aug-21

Sep-21

Oct-21

Nov-21

Dec-21

Jan-22

Feb-22

Mar-22

Apr-22

May-22

Jun-22

Variance

0 - 3 months old

10​

10​

12​

6​

8​

2

3 - 6 months old

7​

7​

7​

9​

7​

(2)

6 - 12 months old

3​

5​

6​

9​

11​

2

Over 12 months old

22​

22​

21​

22​

22​

-

Total Invoices

42​

44​

46​

46​

48​

0​

0​

0​

0​

0​

0​

0​

2

 

[Peter_SSs]

Given that OFFSET is a volatile function I would avoid it where possible.

Could this be any use?

21 12 08.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Invoice Description	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Variance
2	0 - 3 months old	10	10	12	6	8								2
3	3 - 6 months old	7	7	7	9	7								-2
4	6 - 12 months old	3	5	6	9	11								2
5	Over 12 months old	22	22	21	22	22								0
6	Total Invoices	42	44	46	46	48	0	0	0	0	0	0	0	2
Diff

Cell Formulas
Range	Formula
N2:N5	N2	=LOOKUP(9^9,B2:M2)-LOOKUP(9^9,B2:M2,A2:L2)
B6:N6	B6	=SUM(B2:B5)

An alternative for N2 would be

=LET(r,B2:M2,n,COUNT(r),INDEX(r,n)-INDEX(r,n-1))

 

[cazfromoz]

Hello Peter
Seriously, I have no idea what the lookup with the 9^9 means, but it worked perfectly. My next homework is to Google the 9^9 part.
This fabulously great Excel community never ceases to amaze me.
A big thank you.
Caz from OZ

 

[Peter_SSs]

You're welcome.

I have no idea what the lookup with the 9^9 means

It is just a big number. It the lookup cannot find anything that big it returns the last number in the range