Difference between last two cells with offset functionality

cazfromoz

New Member
Joined
Nov 8, 2011
Messages
48
Office Version
  1. 365
Platform
  1. Windows
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 DescriptionJul-21Aug-21Sep-21Oct-21Nov-21Dec-21Jan-22Feb-22Mar-22Apr-22May-22Jun-22Variance
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Given that OFFSET is a volatile function I would avoid it where possible.

Could this be any use?

21 12 08.xlsm
ABCDEFGHIJKLMN
1Invoice DescriptionJul-21Aug-21Sep-21Oct-21Nov-21Dec-21Jan-22Feb-22Mar-22Apr-22May-22Jun-22Variance
20 - 3 months old101012682
33 - 6 months old77797-2
46 - 12 months old3569112
5Over 12 months old22222122220
6Total Invoices424446464800000002
Diff
Cell Formulas
RangeFormula
N2:N5N2=LOOKUP(9^9,B2:M2)-LOOKUP(9^9,B2:M2,A2:L2)
B6:N6B6=SUM(B2:B5)


An alternative for N2 would be
Excel Formula:
=LET(r,B2:M2,n,COUNT(r),INDEX(r,n)-INDEX(r,n-1))
 
Last edited:
Upvote 0
Solution
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top