Search

# Calculate maximum change [homework]

Today, lets see how good your formula skills are.

### Calculate maximum change

Can you calculate what is the maximum change in product sales between 2 months for below data?

Bonus question: Which product is responsible for this change?

Special note: If your formula contains < or > symbols when posting it, use &lt; and &gt; instead. Our commenting system eats up < and > symbols.

Want more Excel challenges?

Try this – more than 25 challenges and problems in Excel.

PS: I got inspiration for this challenge from Mike Girvin’s excellent book – Ctrl + Shift + Enter

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

Thank you and see you around.

### Related articles:

 Written by Chandoo Tags: advanced excel, array formulas, homework, Learn Excel, max() Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 224 Responses to “Calculate maximum change [homework]”

1. Amit says:

={MAX(C1:C6/B1:B6)}

Bonus Question
={INDEX(A1:A6,D8,MATCH(MAX(C1:C6/B1:B6),C1:C6/B1:B6,0))}

• Michael (Micky) Avidan says:

@Amit,
Did you mean: {=MAX(IFERROR(1-(C1:C6/B1:B6),))}

• Kerry says:

Your solution return the maximum positive change not the maximum change. The maximum change occurs in Product 2 with a negative 33% change.

• Michael (micky) Avidan says:

@Kerry,
It is customary to specify the name.

2. Michael (Micky) Avidan says:

For ???? MAX Positive Change:
=MAX(C2:C7-B2:B7)
=INDEX(A2:A7,MATCH(MAX(C2:C7-B2:B7),B2:B7-C2:C7))

For MAX Absolute Change:
=MAX((ABS(C2:C7-B2:B7)))
=INDEX(A2:A7,MATCH(MAX(ABS(C2:C7-B2:B7)),ABS(B2:B7-C2:C7),0))

*** ALL ARRAY FORMULAS !!!
Michael (Micky) Avidan

• Bigger Don says:

Micky...I think you've transposed addresses in Max()'s ABS() functions.
{=INDEX(A2:A7,MATCH(MAX(ABS(C2:C7-B2:B7)),ABS(B2:B7-C2:C7),0)) } should be {=INDEX(A2:A7,MATCH(ABS(MAX(B2:B7-C2:C7)),ABS(B2:B7-C2:C7),0))}

• Michael (Micky) Avidan says:

Not to my opinion...

• Bigger Don says:

Ah! Now I see the difference! I assumed that as in real life situations, because of returns some of the "sales" for a month might be negative.

When I tried your formula I got a different answer than what I would get running it almost by hand. Then I tested with just positives: Your formula gave the right answer, mine the minimum.

The following works with all positives, all negatives, and mixed
{=INDEX(A22:A27,MATCH(ABS(MAX(ABS(B22:B27-C22:C27))),ABS(B22:B27-C22:C27),0))}

3. Eric Xu says:

Max change:{=max(abs(c3:c8-d3:d8))}

Responsible product:{=index(b3:b8,match(max(abs(c3:c8-d3:d8)),abs(c3:c8-d3:d8),0))}

4. Michael (Micky) Avidan says:

According to the Picture (it is always wise to present a link for the workbook) the formulas should read:

For MAX Positive Change Only:
=MAX(C3:C8-B3:B8)
=INDEX(A3:A8,MATCH(MAX(C3:C8-B3:B8),B3:B8-C3:C8))

For MAX Absolute Change:
=MAX((ABS(C3:C8-B3:B8)))
=INDEX(A3:A8,MATCH(MAX(ABS(C3:C8-B3:B8)),ABS(B3:B8-C3:C8),0))

*** ALL ARRAY FORMULAS !!!

Michael (Micky) Avidan

• Barnabas Path says:

When I try your formula, or any of these other formulii, all I can get as a response is either #Name or #Value. What am I missing?

• Jeff Weir says:

Barnabas - Some of these formulas use the IFERROR function that was introduced in Excel 2007, and so won't work on earlier versions. Others are array formulas that need to be entered using Ctrl + Shift + Enter.

• Barnabas Path says:

Thank you Jeff Weir. I'm somewhat of a newbie; researched applying array formulas and just now discovered how powerful that tool is. That was my problem.

5. Faseeh says:

First: =MAX(ABS((C2:C7)-(D2:D7)))
Second: =INDEX(B2:B7,MATCH(C16,ABS((C2:C7)-(D2:D7)),0))

Press Ctrl+Shift+Enter to execute..

6. jraju says:

the formula
=MAX(ABS(c3:c87-d3:d87))
with control+shift+enter for array formula
abs to nullify + or -signs
The answer for the above formula is 40.
Bonus qu.an. Since the product reduces from 120 to 80

7. jraju says:

8. Tigris says:

{=max(abs(c3:c8-d3:d8))}

9. jraju says:

the difference in % for the product 2 is -33.3. hence the max change

10. {=MAX(ABS(C3:C8-D3:D8))}
// Its an array function

11. Eamon says:

Hi Everyone,
Let's say you wanted to catch the biggest increase this month compared to last month. You would use MAX instead of ABS. However, if I use MAX in the array formula I just get #N/A? Why does ABS work and MAX doesn't?

{=INDEX(B2:B7,MATCH(C16,MAX((C2:C7)-(D2:D7)),0))}

12. Michael (Micky) Avidan says:

@Eamon,
Would you be so kind to share with us the "bis secret" U R holding in cell C16 !?
Michael (Micky) Avidan

• Eamon says:

Sorry,

C16 is the returned value from {=MAX(C2:C7)-(B2:B7))}.

• Michael (Micky) Avidan says:

@Eamon,
To my opinion you get it all wrong/mixed up.
In C6 try: =MAX((C2:C7)-(B2:B7)) and for the result try:
{=MATCH(C16,(C2:C7)-(B2:B7),0)} insted of: {=MATCH(C16,(C2:C7)-(D2:D7),0)}
Michael (Micky) Avidan

• Eamon says:

Thanks @Michael (Micky) Avidan.

That worked.

13. Eamon says:

Further to my comment I tried the formulas in Google Sheets using the MAX function and they work?

Weird or bug or feature in Excel 2010?

14. Eamon says:

Spoke too soon - it gives an answer - but the wrong answer!!!

15. Kyle McGhee says:

=AGGREGATE(14,4,ABS((This.Month)-(Last.Month)),1)

Not array entered

• Kyle McGhee says:

or
=AGGREGATE(14,4,ABS(MMULT(C3:D8,{-1;1})),1)
not array entered

• Chandoo says:

+1 for clever use of mmult()

• Ezequiel says:

Hello everyone.
First of all sorry for my poor English.
Arrives a little late to the challenge and was originally my solution:
= {MAX (ABS (C3: C8-D3: D8))}
= {INDEX (B3: B8, MATCH (MAX (ABS (D3: D8-C3: C8)), ABS (D3: D8-C3: C8), 0), 1)}

After reviewing some comments I see that Kyle's answer is very bright.
So my vote is for Kyle. I learn a lot with you.
= AGGREGATE (14, 4, ABS (MMULT (C3: D8; {-1, 1})); 1)
= { INDEX (B3: B8, MATCH (AGGREGATE (14, 4, ABS (MMULT (C3: D8; {-1, 1})); 1), ABS (C3: C8-D3: D8), 0) , 1)}

Greetings.
Ezequiel.

16. Andrew Alexander says:

Just because I don't like array formulas (and LOVE Index!):

Max Absolute change:
=MAX(INDEX(ABS(B1:B6-A1:A6),0))

Max Increase
=MAX(INDEX(B1:B6-A1:A6,0))

Max Decrease
=MIN(INDEX(B1:B6-A1:A6,0))

• Michael (Micky) Avidan says:

@Andrew,
I LOVE blondes but my wife says that coloring the hair harms.
If I'm not mistaken - INDEX (although being a very helpful function) is SEMI-VOLATILE and as such it is re-calculated every time the WB is re-opend.
On the other side - MAX is not volatile.
Michael (Micky) Avidan

• Elias says:

Michael,

INDEX is re-calculated every time the WB is re-open ONLY when it is used to defined a dynamic range. Like =SUM(INDEX(A:A,1):INDEX(A:A,25))

Regards

• Elias says:

Andrew,

You don't like array formulas, but those are array formulas. You just don't confirm them with Ctrl+Shift+Enter

Regards

• Michael (Micky) Avidan says:

@Elias,
U R 100% correct.
The function: SUMPRODUCT works/calculates exactly(!) like {=sum...)} except the need to confirm with CSE - and this is only one example out of many.
Michael (Micky) Avidan

• Andrew Alexander says:

In not too fond of the CSE ones as where I work not many people are aware of them and forget to use CSE when amending them.

• vijay says:

you formula are incorrect

17. Cat2phat says:

I think there should be a secondary bonus, however, it would require changing the data a little.

Change Product 1's This Month value from 120 to 140.

The bonus challenge is to list the products that have the same max absolute change.

18. Michael Rivers says:

Steps
1 - Set Name Ranges for Readability
2 - Find Max Value | {=(MAX(Last_Month-This_Month))}
3 - Find Max Product | {=INDEX(Product,MATCH(MAX(Last_Month-This_Month),Last_Month-This_Month,0),1)}

• Michael Rivers says:

Left out the ABS...didn't fit the population of data but it fits the problem.

1 – Set Name Ranges for Readability
2 – Find Max Value | {=(MAX(ABS(Last_Month-This_Month)))}
3 – Find Max Product | {=INDEX(Product,MATCH((MAX(ABS(Last_Month-This_Month))),ABS(Last_Month-This_Month),0),1)}

19. Ulas says:

easy and usefull 🙂
{=MAX((B1:B6-A1:A6))}

20. Mani says:

{=MAX(\$B\$2:\$B\$7-\$C\$2:\$C\$7)}

21. aissa says:

={Max(Abs([this Month]-[lastMonth]))}

22. Amey says:

Use
{=max (d3:d8-c3:c8)}

23. samtheman says:

{=MAX((C4:C8)-(C3:C7))}

24. Vinod says:

={MAX(C1:C6/B1:B6)}

25. Kevin says:

=(dmax(c3.c8)-dmax(d3.d8))

26. Elias says:

We can get as crazy as we want to get the max value but I think simple is better

Array enter (Ctrl+Shift+Enter)
C11 =MAX(D3:D8-C3:C8)

Just enter
D11 =LOOKUP(2,1/(D3:D8-C3:C8=C11),B3:B8)

Regards

27. Ari says:

You need to treat this as an array formula.

{=MAX((THIS MONTH/LAST MONTH)-1)}

OR

Add an extra column and do the percent change and then subject that column through a MAX function...This is a good way to validate that the first formula is correct.

28. Matthew Redman says:

=MAX(ABS(C3:C8-D3:D8))

29. Gareth says:

=MAX((\$D\$3:\$D\$8-\$C\$3:\$C\$8))

and Product Name

=INDEX(\$B\$3:\$E\$8,MATCH(MAX((\$D\$3:\$D\$8-\$C\$3:\$C\$8)),\$E\$3:\$E\$8,0),1)

30. JMuskett says:

=MAX(E2:E7) = 26.32

Where e2 to e7 contain forumla for % ? eg =(D2/C2-1)*100

31. Mrudula says:

Two solutions proposed:

1)=(MAX(INDEX(ABS((B3:B8-C3:C8)/B3:B8),0))),where B3:B8 is the first column and C3:C8 is the second column as we want largest percentage change its important to consider the abs function. This returns the value 0.33

2)Another variant,convert = MAX(ABS((C3:C8/B3:B8)-1)),convert it to an array formula by means of ctr+shift+enter and you get the same result

32. remcos says:

={MAX(((C2:C7)-(B2:B7))/(B2:B7))}

33. Manoj Gupta says:

prod 1 100 120 20
prod 2 120 80 -40
prod 3 90 75 -15
prod 4 120 99 -21
prod 5 110 110 0
prod 6 95 120 25

25

34. Jason H says:

I went with array formulas:

Max change (in any direction):
{=MAX(ABS(D3:D8-C3:C8))}

Note this form doesn't preserve the direction (sign) of the change.

Identify product:
{=INDEX(B3:B8, MATCH(C11, ABS(D3:D8-C3:C8), 0))}

Where C11 is the already determined max change value from previous formula.

35. Tore Softing says:

Use an array formula like this:
=MAX(D3:D8-C3:C8) - save with Control Shift Enter.

To retrieve the product, you could use this array formula:
=INDEX(B3:B8,MATCH(MAX(D3:D8-C3:C8),(D3:D8-C3:C8),0),1)

36. Jean-Eric says:

Hello,
{=MAX(D3:D8-C3:C8)}
Ctrl + Shift + Enter

Jean-Eric from France

37. Brett Hendley says:

={SUM(MAX(ABS(\$D\$3:\$D\$8-\$E\$3:\$E\$8)))}

38. Mike Park says:

=MAX( ABS(C3:C8-D3:D8)) yields the correct answer of 40
= INDEX(B3:B8, MATCH(MAX( ABS(C3:C8-D3:D8)), ABS(C3:C8-D3:D8),0 ), 1) correctly returns Product 2
both entered as array formulas

39. AKReeves says:

{=MAX(C2:C7-B2:B7)} - Array Formula

40. Arindam says:

Me too went with Array...

Situation A: if we are looking for absolute change from last month to this month -
Ans. 1) {=MAX(ABS((C3:C8)-(D3:D8)))} --- Returns: 40
Ans. Bonus Q.) {=INDEX(\$B\$3:\$B\$8,MATCH(MAX(ABS(\$C\$3:\$C\$8-\$D\$3:\$D\$8)),ABS(\$C\$3:\$C\$8-\$D\$3:\$D\$8),0))} --- Returns: Product 2

Situation B: If we are looking for absolute % change from last month to this month -

Ans. 1) {=MAX(ABS((D3:D8)/(C3:C8)-1))} --- Returns:33% (with % Format)
Ans. Bonus Q.) {=INDEX(\$B\$3:\$B\$8,MATCH(MAX(ABS((\$D\$3:\$D\$8)/(\$C\$3:\$C\$8)-1)),ABS((\$D\$3:\$D\$8)/(\$C\$3:\$C\$8)-1),0))} --- Returns: Product 2

41. Cauê Tavares says:

{=MAX(ABS(B2:B7-C2:C7))}

42. Char N. says:

=MAX((C3-D3),(C4-D4),(C5-D5),(C6-D6),(C7-D7),(C8-D8))
Product 2

43. xtbmlguy says:

[Ctrl]+[Shift]+[Enter] very nice book. Arrived a couple of days ago. Have been exchanging emails with Mike Girvin. Too bad Microsoft didn't produce manuals a tenth as good as his. Lots of great stuff in the book. And a real nice person. We need one on Data Tables. I just sped up an app which used VBA by using a Data Table as index to multicolumn sheet of data: decreased execution time by over 90%.

44. Mrudula says:

Another solution
The premise is same as above,except that I am answering the bonus question now and with a different function. Here goes,

=IF(GCD(B3,C3)=C3,0,GCD(B3,C3))
This returns the following in column D,20,40,15,3,0,5 ,now apply max function and it returns 40

Now apply the formula =INDEX(A3:A8,MATCH(E3,ABS(B3:B8-C3:C8),0))
ctrl+shft+enter to convert to array formula,it returns product 2

45. Michael K says:

With array formulas I used:

Max (%) Change:
{=MAX((K4:K9-J4:J9)/(J4:J9))}

Identify Product: (I created another column on (%) change in column L)
=INDEX(I4:I9,MATCH(J11,L4:L9,0))

Cheers,
Mike

46. Veiga says:

{=MÁXIMO(ABS(D3:D8-C3:C8))}

47. JG says:

=MAXA(B3:C3,B4:C4,B5:C5,B6:C6,B7:C7,B8:C8)

48. JG says:

=MAXA(B3:C3,B4:C4,B5:C5,B6:C6,B7:C7,B8:C8)

49. Arnab says:

{=MAX(B2:B7-C2:C7)}
2 lesson of the book Ctrl + Shift + Enter

50. Mike F. says:

=MAX(ABS(C3:C8-D3:D8))

Remember that since this is an array formula, instead of pressing the ENTER key, do .

This will attach the array formula and the cell will look like this
{=MAX(ABS(C3:C8-D3:D8)}

• Mike F. says:

...(ctrl alt enter) instead of enter -- sorry, this got cut off from my message above.

• Mike F. says:

...sorry again, it is (ctrl shift enter)...how do you edit posts here?

51. Paul says:

I usually don't like array formulas either where you have to do Ctrl+Shift+Enter, but I decided to give it a try because I thought it was probably the only way to get the answer in one formula. I tried {=MAX(ABS(D3:D8-C3:C8))} and to my pleasure, it worked.

Thanks, Chandoo. Fun little homwork assignment.

52. BJ says:

{=INDEX(B3:B8,MATCH(MAX(ABS((C3:C8-D3:D8)/C3:C8)),ABS((C3:C8-D3:D8)/C3:C8),0))}

53. Jim says:

{=MAX(ABS(B2:B7-C2:C7))}

I think this formula answers the question as presented - it gives you the magnitude of the change, but not the direction of the change.

{=MAX(C2:C7-B2:B7)}

This formula would provide for the largest positive change, excluding all negative results.

54. Kieran says:

This will provide the max absolute change and preserve its direction, which seems more useful to me.

=INDEX((C3:C8-B3:B8), MATCH(MAX(INDEX(ABS(C3:C8-B3:B8),,0)), INDEX(ABS(C3:C8-B3:B8),), 0))

55. Lewis Kohnle says:

{=max(abs(D3:D8-C3:C8))}

Max change = product 2 at 40 units

56. Tim says:

All these formulas are well and good to know (absolutely brilliant), but the most important question is, "How does the CUSTOMER (end user) want Max Change defined (positive, negative, ABS, percent change, etc).

Once that is determined, then you can truly wow them with your Excel ninja skills (and save yourself a rewrite).

57. Sudhakar R says:

{=MAX(B1:B6-A1:A6)}

• Sudhakar R says:

it is {=MAX(D1:D6-C1:C6)} where curly brackets comes automatically when we press CTRL+SHIFT+Enter

58. Don says:

{=IF(ABS(MIN(C3:C8-B3:B8))>MAX(C3:C8-B3:B8),MIN(C3:C8-B3:B8),MAX(C3:C8-B3:B8))}

59. Don says:

Did not see the A Column was not used. Try:

{=IF(ABS(MIN(D3:D8-C3:C8))>MAX(D3:D8-C3:C8),MIN(D3:D8-C3:C8),MAX(D3:D8-C3:C8))}

• Don says:

{=IF(ABS(MIN(D3:D8-C3:C8))&gtMAX(D3:D8-C3:C8),MIN(D3:D8-C3:C8),MAX(D3:D8-C3:C8))}

Uses "&gt for >"

• Don says:

Bonus:
{=INDEX(\$B\$3:\$B\$8,MATCH(IF(ABS(MIN(D3:D8-C3:C8))&GTMAX(D3:D8-C3:C8),MIN(D3:D8-C3:C8),MAX(D3:D8-C3:C8)),D3:D8-C3:C8,0))}
Uses "&gt" for >

60. Ariel says:

=MAX(B2:B7-C2:C7)

then Ctrl + Shift + Enter in the cell

61. Meni Porat says:

Array formula:

=MAX((\$C\$3:\$C\$8)-(\$D\$3:\$D\$8))

result=40

62. Gautham (Krish) says:

{=MAX(ABS(C3:C8-D3:D8))} = 40
{=INDEX(B3:B8,MATCH(MAX(ABS(C3:C8-D3:D8)),ABS(C3:C8-D3:D8),0))} = Product2
Simple:
=120-80=40
which is maximum change when you chk individual product, then simply highlight 😉

63. Rifkhan says:

{=MAX(C2:C7-D2:D7,D2:D7-C2:C7)}

64. Rashmi says:

{=MAX(ABS(C3:C8-D3:D8))}

65. vsieffer says:

This formula entered as an array formula calcs the max change
=MAX(ABS(C3:C8-B3:B8))

This formula entered as an array formula calcs the product name located in the first column associated with the max change
=INDEX(A3:C8,MATCH(MAX(ABS(C3:C8-B3:B8)),ABS(C3:C8-B3:B8),0),1)

66. Mohamed Gaber says:

=MAX(C4-D4,C5-D5,C6-D6,C7-D7,C8-D8,C9-D9)

67. Orlando M says:

The question asks for maximum change regardless of the sign of the change so we need to take the magnitude and also think about the problem in terms of percentage. A change from 10 to 20 is greater a change from 100 to 150. In the former case the change is of 100% while in the latter the change is of 50%.

Based on the previous statements one possible option would be:

{=MAX(ABS(C3:C8-D3:D8)/C3:C8)}

The bonus question would be solved as follows:

{=INDEX(B3:B8,MATCH(MAX(ABS(C3:C8-D3:D8)/C3:C8),ABS(C3:C8-D3:D8)/C3:C8,0))}

{=MAX(B2:B7-C2:C7)}

69. Uri Weiss says:

{=MAX(ABS((C3:C8-B3:B8)/B3:B8))}

Note: Array Formula

70. Uri Weiss says:

{=MAX(ABS((C3:C8-B3:B8)/B3:B8))}

Confirmed by Cntrl Shit Entre

{=MAX(ABS(C2:C8)-ABS(D2:D8))}

72. Ronald says:

Its an array formula that calculates the max of the absolute value of the variance [This month] - [Last month]
={MAX(ABS((D3:D8-C3:C8)))}

73. Gianluca says:

Hi Chandoo,
I recommend the following 2 CSE-solutions:

max absolute change
{=max((D3:D8)-(C3:C8))}
max relative change
{=max((D3:D8)/(C3:C8)-1)}

Kind regards, Gianluca

74. Alok Joshi says:

=Max(C3:C8-D3:D8)
entered as an array formula

75. Alex Ashin says:

{=INDEX(J3:L8,MATCH(MAX(K3:K8-L3:L8),K3:K8-L3:L8,0),1)}

76. Roger Spire says:

Bem a resposta é

{=MÁXIMO(ABS((D3:D8-C3:C8)/C3:C8))}

77. Donna Swoope says:

=(d2-c2)/c2
copy the formula for rows 3 thru 8
then highlight the column with the changes in it, and use conditional formatting to highlight the highest number.

78. Mark Wiley says:

{=MAX(ABS(I5:I10-J5:J10))}

79. Mark Wiley says:

Sorry, I put data in rows I & J instead of C & D.

80. Hi Chandoo,

Here is my solution ..

=MAX(C3:C8-B3:B8) CTRL+SHIF+ENTER

81. Mukesh says:

I think this formula will find out max change

=sum(max(cell reference of this month-cell reference of last month))

and press control shift enter

82. DavidRaj says:

{=MAX(C3:C8-D3:D8)}

83. Mamith V says:

{=MAX(ABS(C3:C8-D3:D8))}

84. ravi says:

=(max(c3:c8)-min(c3:c8))

85. Sapan Dighe says:

I used the following formula with Ctrl+Shift+Enter
=MAX(ABS(C2:C6-B2:B6), ABS(B2:B6-C2:C6))

This will give me max difference irrespective of numerical value in these cells.

• Sapan Dighe says:

Sorry for the mistaken row numbers. The formula with corrected row numbers is as follows:-(to be used with Ctrl+Shift+Enter)
=MAX(ABS(C3:C8-B3:B8), ABS(B3:B8-C3:C8))

• Sapan S Dighe says:

Pardon me for the mistaken row numbers. The formula with corrected row numbers is as follows:-

=MAX(ABS(C3:C8-B3:B8), ABS(B3:B8-C3:C8))

(to be used with Ctrl+Shift+Enter)

86. NK Jaketia says:

{=Max(d3:d8-c3:c8)}

87. KM Zachariah says:

{=MAX(C3:C8-D3:D8)}

88. parisa says:

=max(C1:c8-d1:d8)

• parisa says:

cntrl+shift+enter

89. mahesh says:

no

90. Mukundan says:

=max(large(\$C\$2:\$C\$8,1),large(\$d\$2:\$d\$8,1))

91. Vijay says:

=INDEX(\$A\$2:\$A\$7,MATCH(MAX((\$C\$2:\$C\$7)-\$B\$2:\$B\$7),((\$C\$2:\$C\$7)-(\$B\$2:\$B\$7)),0),0)

• Vijay says:

=INDEX(\$A\$2:\$A\$7,MATCH(MAX((\$C\$2:\$C\$7)-\$B\$2:\$B\$7),((\$C\$2:\$C\$7)-(\$B\$2:\$B\$7)),0),0) with CSE

• Vijay says:

=INDEX(\$A\$2:\$A\$7,MATCH(MAX(ABS((\$C\$2:\$C\$7)-\$B\$2:\$B\$7)),ABS(((\$C\$2:\$C\$7)-(\$B\$2:\$B\$7))),0),0) with CSE

92. Johnson Mathias says:

Highest Value Change (HVC)
HVC Product Name
=INDEX(B3:B8,MATCH(MAX(ABS(C3:C8-D3:D8)),ABS(C3:C8-D3:D8),0),1)

HVC Sales Diff Value
=MAX(ABS(B3:B8-C3:C8))

HVC % Diff (first identify the HVC value & derive %)
=MAX(ABS(C3:C8-D3:D8))/INDEX(C3:C8,MATCH(MAX(ABS(C3:C8-D3:D8)),ABS(C3:C8-D3:D8),0),1)

Highest Percentage Change (HPC)
HPC Product Name
=INDEX(B3:B8,MATCH(MAX(ABS(C3:C8-D3:D8)/C3:C8),ABS(C3:C8-D3:D8)/C3:C8,0),1)

HPC Sales Diff Value (first identify the HPC Percentage & derive Value)
=ABS(INDEX(C3:C8,MATCH(MAX(ABS(C3:C8-D3:D8)/C3:C8),ABS(C3:C8-D3:D8)/C3:C8,0),1)-INDEX(D3:D8,MATCH(MAX(ABS(C3:C8-D3:D8)/C3:C8),ABS(C3:C8-D3:D8)/C3:C8,0),1))

HPC % Diff
=MAX(ABS(B3:B8-C3:C8)/B3:B8)

93. Tamal Ghosh says:

Max %Change :
=INDEX(A3:A19,MATCH(MAX(E3:E19),E3:E19,0))&" : "&ROUND(LARGE(E3:E19,1),0)&"%"

Max Change (Absolute) :
=INDEX(A3:A19,MATCH(MAX(D3:D19),D3:D19,0))&" : "&ROUND(LARGE(D3:D19,1),0)

i've create another two column to calculate absolute change in "D" column & %change in "E" column.
answer of 'Bonus Question' included in above formula.

94. Punet says:

=Max(d3:d8-c3:c8) - array formula

95. Johnson Mathias says:

all above formulas are entered by Ctrl Shift Enter

96. Seth Strandin says:

Hi everyone.
Being on a Power Pivot trip I did it this way:
Months on columns, Products on rows
Calculated field:
([ThisMonthSales]-[LastMonthSales])/[ThisMonthSales]
Sorted Largest to Smallest, and based on the greatest change positive or negative was -33,3% for Product 2.
I know this was not a Data Model, but I tested it on Snacks 'R Us.
Power Pivot is awesome, and thanks Chandoo for the Power Pivot University on line. From now on I will probably use PP for most of my analysis tasks.

97. Yatin says:

=MAX(ABS(F4:F9-E4:E9))

98. SAMBIT KUMAR MOHAPATRA says:

=MAX(ABS(C5:C10-B5:B10))

Press (CTRL+SHIFT+ENTER) KEY instead of ENTER KEY

99. maximum change in product sales :
{=MAX(D3:D8-C3:C8)}
result : 25

Which product is responsible for this change?
{=INDEX(B3:B8;EQUIV(MAX(D3:D8-C3:C8);D3:D8-C3:C8;0))}
result : product 6

100. RobertThi says:

=MAX(C2:D8)

101. javhkhlantugs says:

=LARGE(ABS(D3:D8-C3:C8),1)

102. Mrunal Meher says:

Used as an array formula:

=INDEX(B4:B9,MATCH(MAX(ABS(D4:D9/C4:C9-1)),ABS(D4:D9/C4:C9-1),0))&" is responsible for the change with "&TEXT(MAX(ABS(D4:D9/C4:C9-1)),"##.0%")&" change"

103. John Kragh says:

=MAKSV(ABS(B3-C3);ABS(B4-C4);ABS(B5-C5);ABS(B6-C6);ABS(B7-C7);ABS(B8-C8)) = 40

104. Khalid says:

for max positive change
{=MAX(B2:B7-C2:C7)} with Ctrl+Shift and Enter
For bonus question
{=INDEX(A2:A7,MATCH(C8,B2:B7-C2:C7,0),)} Again an array formula will go with ctrl+sshift+enter

105. Naveen BP says:

{=MAX(ABS(E9:E14-F9:F14))}
retursns 40

106. Thor says:

I found the easiest way to do this was to use an array formula to get the biggest difference:

=MAX((D2:D7-C2:C7))

And in order to get the product, I'd first use the match function to find out which difference was the largest and then use the index function to find the equivalent position in the product string. This formula is also entered as an array formula using ctrl+shift+enter. I live in Denmark so in the formulas I use ";" instead of "," as argument separator.

=INDEX(B2:B7;MATCH(B11;D2:D7-C2:C7))

107. hassan says:

{=MAX(B1:B6-C1:C6)}

108. Alexis says:

=MAX(ABS(\$D-\$C)

109. Ankit Chopra says:

=MAX(K3:K8-J3:J8)

K3:K8 = This Month Column
J3:J8 = Last Month Column

Instead of pressing enter, press Ctrl + Shft + Enter ( Array Formula)

110. Jake says:

Knowing the absolute change alone is not terribly meaningful, imo, if you don't know the sign of the change also, so I went this route:

Calculating Maximum Change:
{=IF(MAX(C2:C7-B2:B7)>=MAX(ABS(C2:C7-B2:B7)),MAX(C2:C7-B2:B7),MIN(C2:C7-B2:B7))}

- I first check whether the max of the difference is at least the max of the absolute difference. If it is, then I know that the largest difference is positive, so I return the value of the difference. If it isn't then I know that the largest difference is negative, so I return the value of the smallest difference, i.e., the most negative number.

Bonus Question:
=INDEX(A2:A7,MATCH(IF(MAX(C2:C7-B2:B7)>=MAX(ABS(C2:C7-B2:B7)),MAX(C2:C7-B2:B7),MIN(C2:C7-B2:B7)),C2:C7-B2:B7,0),1)

- This is just the classic INDEX + MATCH reverse VLOOKUP. The index range is just the list of product names, the second argument is just the answer to the max change question, the third is just 1 since my index range has only one column.

• Jake says:

Sorry, the bonus question answer should also be an array formula. Here's a corrected version:

Bonus Question:
{=INDEX(A2:A7,MATCH(IF(MAX(C2:C7-B2:B7)>=MAX(ABS(C2:C7-B2:B7)),MAX(C2:C7-B2:B7),MIN(C2:C7-B2:B7)),C2:C7-B2:B7,0),1)}

111. Yogesh says:

First: =MAX(ABS((C2:C7)-(D2:D7)))Second:=INDEX(B2:B7,MATCH(C16,ABS((C2:C7)-(D2:D7)),0))

112. Rupesh Kumar says:

We can calculate maximum change by using the array formula.
{=Max(C3:C8-D3:D8)}

113. Frank Bernard says:

With your help (how to enter a matrix formula)
{=MAX(ABS(D3:D8-C3:C8))}

114. Jude Shyju says:

Assuming data in A1:C7,

Max Change : {=MAX(ABS(B2:B7-C2:C7))}
Product Name : {=INDEX(A2:C7,MATCH(C9,(ABS(B2:B7-C2:C7)),0),1)}

Thanks,
Jude Shyju

115. Emil Lascau says:

=MAX(D2-C2,D3-C3,D4-C4,D5-C5,D6-C6,D7-C7)

Hi Chando

I used this formula:

=MAX(C3:C8-D3:D8) then hit [CTRL] + [SHIFT] + [Enter] for array

Thanks

Viraj

117. Alok Kumar Jena says:

=MAX((ABS(D2:D7-C2:C7)))

Enter Ctrl+Shift+Enter

118. Sheela A.M says:

=Max()

119. Ron Wallace says:

Calculate Maximum Change Homework
Prod in col B, last month col C, this month col D
For max difference =LARGE(D4:D9-C4:C9,1), use S-C-E
For product producing max =INDEX(\$B\$4:\$B\$9,MATCH(MAX(\$D\$4:\$D\$9-\$C\$4:\$C\$9),\$D\$4:\$D\$9-\$C\$4:\$C\$9,0)), use S-C-E

Arrays are neat. [Yes, I know I could have used the MAX() in an array to find the max difference, but wanted to show an alternate method]

120. hooroy says:

Challenge #1 solution:
{=MAX(ABS(C3:C8-D3:D8))}
Array entered

• Juda Phali says:

Product 6

121. Anil Arora says:

{=MAX(ABS((C2:C7-B2:B7)/B2:B7))}

122. Rishab says:

25
Product 6

• Nik says:

Correct answer is 40. product 2

123. Nik says:

=max(abs(c3-d3),abs(c4-d4),abs(c5-d5),abs(c6-d6),abs(c7-d7),abs(c8-d8))

124. John Michaloudis says:

={LARGE(ABS((D3:D8)-(C3:C8)),1)}

={INDEX((B3:B8),MATCH(LARGE((C3:C8)-(D3:D8),1),(C3:C8)-(D3:D8),0))}

125. Chus says:

={MAX((C2:C5)/(B2:B5)-1)}

126. Henk Stander says:

'{=MAX(ABS((D3:D8)-(C3:C8)))}

127. waqar says:

Max Change 25 {=LARGE((\$E\$3:\$E\$8)-(\$D\$3:\$D\$8),1)}

Max Change 25 { =MAX((\$E\$3:\$E\$8)-(\$D\$3:\$D\$8))}

Product Max Change Product6 { =INDEX(\$B\$3:\$E\$8,MATCH(MAX((\$E\$3:\$E\$8)-(\$D\$3:\$D\$8)),(\$E\$3:\$E\$8)-(\$D\$3:\$D\$8),0),1)}

128. RACH says:

{=MAX((B3:B8)/(A3:A8)-1)}

• RACH says:

Sorry - the last result was based on my own workbook.
based on Chandoo's version, below the result
{=MAX((D3:D8)/(C3:C8)-1)}

• RACH says:

in case of cosidering all upward/downward trend, following will work:

{=MAX(ABS(MAX((\$D\$3:\$D\$8)-(\$C\$3:\$C\$8))),ABS(MIN((\$D\$3:\$D\$8)-(\$C\$3:\$C\$8))))}

129. Olav says:

=MAX(MAX(D2:D7-C2:C7),MAX(C2:C7-D2:D7))
entered as an array formula will find the max difference whichever column has the high and low number.

130. C.Treffner says:

{=MAX(ABS(C3:C8-D3:D8))}

131. Marco Magalhães says:

Consider the following Arrays
ProductName=A2:A7
lm=B2:B7
tm=C2:C7

=INDEX(ProductName;MATCH(MAX(tm-lm);(tm-lm);0);1) & " with " & MAX(tm-lm)

132. Pablo says:

Hello
An array formula will do the trick:

{=MAX(C3:C8-B3:B8)}

showing 25, which is the right answer

133. canapone says:

Hi,

in B10 absolute max difference

=MAX(INDEX(ABS(B3:B8-C3:C8),0))

in B11 related product name

=INDEX(A3:A8,MATCH(B10,INDEX(ABS(B3:B8-C3:C8),0),0))

Regards
Regards

134. Chris says:

My formula entered as an array
=LARGE(D2:D7-C2:C7,1)

Regards

Chris

135. Kevin says:

For the max (input into cell B9):
{=(MAX(ABS(B2:B7-C2:C7)))}

For the bonus question:
{=INDIRECT(CONCATENATE("A",MATCH(B9,ABS(B1:B7-C1:C7),0)))}

136. Hanlie Croeser says:

{=MAX(C2:C7/B2:B7-1)}

137. JanWim says:

= max(c3..c8)-min(c3..c8)

138. Jerry Giles says:

=max(c3-d3,c4-d4,c5-d5,c6-d6,c7-d7,c8-d8)

139. young-Mi Yoo says:

{=MAX(ABS(B2:B7-C2:C7))}

• Luigi Neri says:

The formula of young-Mi Yoo works.

For the bonus question the answer is :

{=INDEX(\$B\$2:\$B\$7,MATCH(MAX(ABS(C2:C7-D2:D7)),ABS(C2:C7-D2:D7),0))}

Tested and it works. Thanks for having taught me this nice trick.

140. Peter Lang says:

=max(D3:D8-C3:c8) and Press Ctrl-Shit-Enter and the formula will be change to this: {=max(D3:D8-C3:c8)}

141. Hi Sir,
Please find the below formula ..it is working perfect.
Please let me know is it correct ...way ..
Please also provide us better way.
={LARGE(\$D\$2:\$D\$6-\$C\$2:\$C\$6,ROW(A1))}

142. ={LARGE(\$D\$2:\$D\$6-\$C\$2:\$C\$6,ROW(A1))}

143. ={MAX(D3:D8-C3:C8)} The {} is entered by excel because it is an array function

144. Rudra Sharma says:

Sorry for responding late...
My formula is
=MAX((D2:D7)-(C2:C7))
this is an array formula.

With Regards
Rudra

145. […] Friday, we had a fun little Excel challenge – Calculate Maximum Change. More than 170 people commented and shared their solutions to this […]

just find the difference of c and d in e and use max formula in c11.

147. Abhi says:

{=MAX(IF(\$K\$3:\$K\$8-\$J\$3:\$J\$8<0,(\$K\$3:\$K\$8-\$J\$3:\$J\$8)*-1,\$K\$3:\$K\$8-\$J\$3:\$J\$8))}

148. Vivek says:

={INDEX(A2:A7,MATCH(MAX(ABS(C2:C7-B2:B7)),ABS(C2:C7-B2:B7)),0)}

149. Ben Mosbeh Mohamed Slim says:

{=Max(ABS(C3-D3))} in absolute terme
=MAX(ABS(C3:C8-D3:D8)/C3:C8) Max rate of change

150. =MAX(\$C\$2:\$C\$7-\$B\$2:\$B\$7)

151. PSG says:

=MAX(ABS((B3:B8)-(C3:C8)))

Press C+S+E (Array Function)

152. Daffy says:

Max change {=MAX(ABS(C3:C8-D3:D8))}
Max change product {=INDEX(B:B,MATCH(C11,ABS(C:C-D:D),0),)}

153. Mark says:

Create table & use array formulas

Absolute Max Change
{=MAX(ABS(MyProducts[This Month] - MyProducts[Last Month]))}

Product Responsible
{=INDEX(MyProducts[Product],MATCH(MAX(ABS(MyProducts[Last Month] - MyProducts[This Month])),ABS(MyProducts[Last Month]-MyProducts[This Month]),0))}

154. Mustafa says:

{max(D3:D8-C3:C8)} Its an array formula, and we do that with pressing Ctrl+Shift+Enter

155. Laksiri says:

Max Change [on C11] : =MAX(D3:D8-C3:C8)
Product [on C12] : =OFFSET(B1,SUM(IF(D3:D8-C3:C8=C11,ROW(D3:D8),0)),0)

156. Ashok says:

CSE formula =MAX(D3:D8-C3:C8)

157. Prerit says:

Max change value =MAX(C2:C7-B2:B7)
Max change product = =INDEX(A2:A7,MATCH(MAX(C2:C7-B2:B7),C2:C7-B2:B7,0),1)

158. marvin says:

CSE=MAX(ABS(C3:C8-D3:D8))

159. Vivek S says:

={INDEX(B3:B8,MATCH(MAX(C3:C8-D3:D8),C3:C8-D3:D8,0))}

160. Matt Healy says:

All very clever, but I want something simple enough that when I open the file six months from now, I can understand what I did. Kernigan and Plauger warned against being too clever at the expense of clarity. So I would just add an auxiliary column with a formula for the change. Then I can use a simple Max formula to get its max, and find which it s using Max and Offset.

161. Md. Nazmul Muneer says:

=INDEX(B3:B8,MATCH(MAX(ABS((C3:C8-D3:D8))),ABS(C3:C8-D3:D8),0))
Ctrl+Shift+Enter

162. Fay says:

Ans 1: =MAX(ABS(D3:D8-C3:C8))(Ctrl+Shift+Enter)
Ans 2: =INDEX(B3:B8,MATCH(MAX(ABS(D3:D8-C3:C8)),ABS(D3:D8-C3:C8),0)) (Ctrl+Shift+Enter)

163. Maria says:

=MAX((C3-D3),(C4-D4),(C5-D5),(C6-D6),(C7-D7),(C8-D8))

164. Preeti says:

ans 1. =MAX((ABS(C3:C8-B3:B8))/B3:B8)*100

ans 2.=INDEX(A3:A8,MATCH(MAX(((ABS(C3:C8-B3:B8))/B3:B8)*100),(((ABS(C3:C8-B3:B8))/B3:B8)*100),0))

165. BVGPitt says:

I still can't get rid of the #VALUE! for any of these answers above.
It's reading the formula as an error. I am using Excel 2010. I have tried everything.

• Preeti says:

hi, are you using ctr+shift+enter ? these are array formuale so you can't simply enter !

• BVGPitt says:

I tried that before but now realize now you have to do that while the window is still open --- thank you very much! I was doing it after I created the formula!

166. Koel Ray says:

=MAX( ABS(B2:B7-C2:C7))

• Koel Ray says:

make it an array calculation with ctrl + shift +enter

167. Damien says:

=IF(MAX(D74:D76-C74:C76)&GT=ABS(MIN(D74:D76-C74:C76)),MAX(D74:D76-C74:C76),MIN(D74:D76-C74:C76))

Will tell you the max positive or negative difference I think.

Damien 🙂

168. Thiago Cardoso says:

I used:

#1:
{=MAX((Tabela1[This month]-Tabela1[Last month])*-1)}

#2:
{=INDEX(Tabela1[Product];MATCH(MAX((Tabela1[This month]-Tabela1[Last month])*-1);(Tabela1[This month]-Tabela1[Last month])*-1);0)}

169. Ajay Rajguru says:

Question: Which product is responsible for this change?

=INDEX(B3:B8,MATCH(B12,INDEX(C3:C8-D3:D8,0),0),1)

170. Johne305 says:

Its actually a nice and helpful piece of information. Im glad that you shared this useful information with us. Please keep us up to date like this. Thanks for sharing. cabegfabeekg

171. John Fuller says:

{=MAX(ABS(C2:C7-D2:D7))}

172. Suresh TNVB says:

{=MAX(B2:B7-C2:C7)}

173. Jo says:

={C1:C6-B1:B6} I think this is the simplest formula

• Amanda says:

{=MAX((L11:L16)-(M11:M16))}

174. candychung says:

={max(abs(c3:C8-D3:D8))}

175. Bigger Don says:

{=MAX(ABS(B2:B7-C2:C7))}
IOW 40...

First I tried to wrap my head around array functions from a theoretical standpoint but since I was a Business major and not a math or engineering person, I figured it was beyond me, some sort of esoterica discussed over rabidly by people too nerdy to be gamers. (ahem! If someone doesn't see I'm kidding, I'm sorry)

Then I saw the vlookup array function. OK. That's cool. I do a lot of vlookups and it could save me some headaches and time.

But this challenge made all my doubts go away that I might someday understand a little bit what's going on with array formulas! Thanks for an easy one to get me started!

176. Demid says:

{=INDEX(\$A\$2:\$A\$7,MATCH(MAX(ABS((C2:C7)/(B2:B7)-1)),ABS((C2:C7)/(B2:B7)-1),0))&" ("&ROUND(MAX(ABS((C2:C7)/(B2:B7)-1)),2)&"%)"}

Gives "Product 2 (33%)"

177. jay sharma says:

pklz send me tric

178. karan says:

=MAX(ABS(c3:c87-d3:d87))

179. Gurminder Singh Puri says:

Step 1. A new Column will be created in Cell D1 , named " Difference Multiplied by (-)1 and the following formula will be entered in Cell D2 :=IF((C2-B2)/B2%<0,((C2-B2)/B2%)*-1,(C2-B2)/B2%)
Step 2. The following Formula will be entered in Cell C11 to find the Maximum Change Value : =MAX(D2:D7)
Step 3. Now to find the Product responsible for this change, the following formula will be entered in Cell C13 :=INDEX(A2:D7,MATCH(C11,D2:D7,FALSE),1)
Explanation : When we have ascertained that Max. Change ( Either Positive or Negative in the provided data is 50, we have to find out the Row # which contains value 50. This is done by use of the formula =MATCH(C11,D2:D7,FALSE) , which returns the Row # as 2, from range D2:D7 and when we incorporate this Match Formula in the above written Index formula, it returns the value in Col 1 of the range A2:D7.

180. PeterEruteya says:

The answer is solved using Array Ctrl+Shift+Enter

{=MAX(B2:B8-A2:A8)}

Thanks

181. Abhijit Mandal says:

{=MAX(ABS(B2:B8-C2:C8))}

 « Free Invoice Template using Excel – Download Why you should close down Excel completely »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.