Excel formula comparison

[RD1982]

Hi Excel Gurus - Could you pls help me to find a formula that will give me the comparison between below table - from previous to current week - of main 3 lanes impacted (increase/decrease %)?

For instance, from TAS > VIC on WC26 was performance 80% but next week (WC 04) dropped to 60%, the formula would let me know the combination of "from/to" from one week to another which "from/to" had the highest impact by increasing/decreasing %.

WC 26​			WC 04
From	To	%	From	To	%
QLD	SA	100%	QLD	SA	90%
QLD	TAS	100%	Sydney	VIC	70%
TAS	VIC	80%	TAS	VIC	60%
Sydney	VIC	60%	Sydney	Perth	50%
Sydney	Perth	50%	QLD	TAS	20%

 

[Eric W]

Do you mean something like this?

Book2
	A	B	C	D	E	F	G	H	I	J
1	WC 26			WC 04				Comparison
2	From	To	%	From	To	%		From	To	Change
3	QLD	SA	100%	QLD	SA	90%		QLD	TAS	-80%
4	QLD	TAS	100%	Sydney	VIC	70%		TAS	VIC	-20%
5	TAS	VIC	80%	TAS	VIC	60%		QLD	SA	-10%
6	Sydney	VIC	60%	Sydney	Perth	50%		Sydney	VIC	10%
7	Sydney	Perth	50%	QLD	TAS	20%		Sydney	Perth	0%
Sheet7

Cell Formulas
Range	Formula
H3:J7	H3	=LET(a,A3:A7,b,B3:B7,t,CHOOSE({1,2,3},a,b,SUMIFS(F3:F7,D3:D7,a,E3:E7,b)-C3:C7),SORTBY(t,ABS(INDEX(t,0,3)),-1))
Dynamic array formulas.

 

[RD1982]

Hi @Eric W - Amazing your formula!!! I never used LET at all even less with the combination you have done genius. I cannot replicate into my file I may be missing something see below. Help?

As I use 2 different reports after SUMIF I need to capture from last week as shown below...I didn't get the concept of "t 1, 2, 3" neither the end as "t" index (t,0,3) is the 3 the related to column C %?

 

[Eric W]

Let's see if I can clear things up a bit. LET is a relatively new function. It basically allows you to define some intermediate variables, then string several functions together, without having to redefine each variable every time you want to use it. I used some basic variable names, but they weren't clear as to what they are. So some of them you probably didn't know whether to add a sheet reference or not. So I rewrote it with some more understandable names (I hope!) and put them all up front, so you don't have to go too deep to modify it. If your last week sheet looks like:

Book2
	A	B	C	D	E	F	G	H	I
1		From	To						%
2		QLD	SA						100%
3		QLD	TAS						100%
4		TAS	VIC						80%
5		Sydney	VIC						60%
6		Sydney	Perth						50%
Last Week

Then the "This Week" sheet should look like:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		From	To						%
2		QLD	SA						90%			QLD	TAS	-80%
3		Sydney	VIC						70%			TAS	VIC	-20%
4		TAS	VIC						60%			QLD	SA	-10%
5		Sydney	Perth						50%			Sydney	VIC	10%
6		QLD	TAS						20%			Sydney	Perth	0%
This Week

Cell Formulas
Range	Formula
L2:N6	L2	=LET(TWSupp,B2:B6,TWRec,C2:C6,TWPct,I2:I6,LWSupp,'Last Week'!B2:B6,LWRec,'Last Week'!C2:C6,LWPct,'Last Week'!I2:I6,dif,SUMIFS(TWPct,TWSupp,LWSupp,TWRec,LWRec)-LWPct,table,CHOOSE({1,2,3},LWSupp,LWRec,dif),SORTBY(table,ABS(INDEX(table,0,3)),-1))
Dynamic array formulas.

The formula is (I added a few line feeds for clarity):

=LET(TWSupp,B2:B6,
          TWRec,C2:C6,
          TWPct,I2:I6,
          LWSupp,'Last Week'!B2:B6,
          LWRec,'Last Week'!C2:C6,
          LWPct,'Last Week'!I2:I6,
          dif,SUMIFS(TWPct,TWSupp,LWSupp,TWRec,LWRec)-LWPct,
          table,CHOOSE({1,2,3},LWSupp,LWRec,dif),
          SORTBY(table,ABS(INDEX(table,0,3)),-1))

Let me break it down a bit for you. The first variable is TWSupp (This Week Supplier), then TWRec (This Week Receiver), then TWPct (This Week Percent), then LWSupp, LWRec, and LWPct. So you should see pretty easy which ones need the sheet references.

The next variable is dif (difference). This is an intermediate result. The SUMIFS uses the already defined variables (ranges) to find the "This Week" percentages in the same order they are listed in the "Last Week" sheet. Then we subtract the LWPct to get the difference (change).

The next variable is table, another intermediate result. The CHOOSE function is used here to create a table with 3 columns, LWSupp, LWRec, and dif.

And finally we have the SORTBY. This sorts the table we just made by the absolute value of the third column. INDEX(table,0,3) - The 0 means we want all the rows, the 3 means the 3rd column like you surmised.

Hopefully this clears things up a bit! If not, let me know.