Monthly balance difference - find highest variation

[ChetManley]

Hi everyone,

I have a table with 12 columns containing monthly balances of our customers:

Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
10	3.5	55	1	90	500	123	44	44.5	31.2	0	900

I need to calculate the variation between consecutive months (Jan to Feb, Feb to March, so on..) and return the highest of these variations (in above example would be Nov to Dec - +900).

Even though this can be easily done, albeit with several steps - i.e. calculate the variation between each month separately by adding extra columns - and then doing a MAX on those cells.
Is there a more elegant solution here that I'm missing?
I'm open to any type of solution (VBA, formula, etc)

Much appreciated,
Chet

 

[jmacleary]

Hi there. You can do it with an array formula like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	10	3.5	55	1	90	500	123	44	44.5	31.2	0	900
3
4				900
Sheet1

Cell Formulas
Range	Formula
D4	D4	{=MAX(A2:L2-B2:M2)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Remember that the range formula (in D2) must be entered using Ctrl-shift-enter and don't type the curly brackets.

 

[ChetManley]

Hi there. You can do it with an array formula like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	10	3.5	55	1	90	500	123	44	44.5	31.2	0	900
3
4				900
Sheet1

Cell Formulas
Range	Formula
D4	D4	{=MAX(A2:L2-B2:M2)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Remember that the range formula (in D2) must be entered using Ctrl-shift-enter and don't type the curly brackets.

Hi @jmacleary ,

That's great! Only issue is it doesn't pick up negative variations!

e.g. If Feb = 100 and Mar = 900 and Apr = 1000

the formula will return March to April (+100) instead of Feb to March (-800).

Would {=MAX(ABS(A2:L2-B2:M2))} bypass this?

Cheers
Chet

 

[jmacleary]

Yes it would - of course strictly speaking a positive value is always greater than a negative one! However, I was a bit lax there. Be aware that you will always get a positive result

 

[ChetManley]

Solved - thank you