Nov 17 2022 06:33 AM - edited Nov 17 2022 06:33 AM
Hello everyone,
my rows contain different KPIs and their values. The columns state the according month of the value entry per KPI (column B-M depict Jan22 to Dec22).
Via the LOOKUP function I was able to select the ongoing current value that was entered per row.
I'd like to know how I can calculate the current value (already foun it via lookup) minus the last value (the value before the current value).
Function as a question would be: What is the value before my latest entry in this row? (Excluding empty cells).
If that is only possible via VBA, pls provide the code and if possible one or two sentences with instructions.
Thank you all!
Nov 17 2022 07:00 AM
To provide a solution for this request, we're going to need to know what your vertical intersection looks like. The months run left-to-right in columns. Do you have numbers representing days of the month running vertical like this?
Also, if you're using LOOKUP, it suggests you might be on an older version of Excel. Which version do you have?
Nov 17 2022 07:14 AM
Excel version: Version 2202 Build 16.0.14931.20764 64 Bit
Somehow, I can't send a screenshot...
Vertical/rows: Different KPIs in the first column and the columns after are values regarding the KPI in the respective row and the date stated in the row above. So it is really just a calculation within one row.
Nov 17 2022 07:18 AM
According to your screenshot:
Imagine your days would be KPIs and I would like to know what the delta between your current value of KPI 17 and the value before the current (in this case629).
Remember it has to be dynamic due to the fact that there wont be forecasts so the latest value always equals the current value and there can be empty cells in a row if the KPI is tracked monthly, quarterly etc.
Thanks!
Nov 17 2022 07:36 AM
SolutionThis may work for you:
=LET(range,A1:M26,r,XMATCH(TRUE,A:A,1)-1,c,XMATCH(TODAY(),A1:Z1,1),Current,INDEX(range,r,c),Previous,INDEX(range,r,c-1),Current-Previous)
Nov 18 2022 01:17 AM - edited Nov 18 2022 01:33 AM
Thank you so much @Patrick2788
Works perfectly if there are no empty cells.
Can you think of a solution if for example the value 629 doesn't exist=empty cell?
The ideal return would therefore be: 35 - 845 = -810
Basically, ignoring the empty cell and going with the next lower entered value.
My current solution would be:
Previous,IF((INDEX(range,r,c-1)=0),INDEX(range,r,c-2),(INDEX(range,r,c-1))
Unfortunately, I would need to extend the If clauses up to 11 blank cells because that is the maximum number of blank cells that could occur.
Is there a smarter way?
Thank you!
Nov 18 2022 05:46 AM
Try this one:
=LET(range,A1:BA26,r,XMATCH(TRUE,A:A,1)-1,c,XMATCH(TODAY(),A1:AA1,1),Current,INDEX(range,r,c),Last,INDEX(range,r,SEQUENCE(,50)),f,FILTER(Last,Last>0),INDEX(f,1,COLUMNS(f))-INDEX(f,1,COLUMNS(f)-1))
Nov 18 2022 06:08 AM
Nov 18 2022 06:15 AM
Nov 21 2022 04:20 AM - edited Nov 21 2022 04:23 AM
thank you for your reply!
Do you see something wrong with my function?
If I expand my selection for "range" and "c" a value returns. However, the value doesn't seem to be a calculation but more like an error within my formula.
Thanks in advance!!
Nov 21 2022 05:57 AM
The 50 is the problem. It may be best to pull the SEQUENCE number based on the rows in 'range':
=LET(range,C2:AQ27,r,XMATCH(TRUE,C:C,1)-1,c,XMATCH(TODAY(),C2:AQ2,-1),Current,INDEX(range,r,c),Last,INDEX(range,r,SEQUENCE(,ROWS(range))),f,FILTER(Last,Last>0),INDEX(f,1,COLUMNS(f))-INDEX(f,1,COLUMNS(f)-1))
Nov 17 2022 07:36 AM
SolutionThis may work for you:
=LET(range,A1:M26,r,XMATCH(TRUE,A:A,1)-1,c,XMATCH(TODAY(),A1:Z1,1),Current,INDEX(range,r,c),Previous,INDEX(range,r,c-1),Current-Previous)