Calculate maximum change [homework]

Posted on March 21st, 2014 in Formula Challenges - 212 comments

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?

Calculate maximum change - Excel formula homework

 

Bonus question: Which product is responsible for this change?

Post your answers in comments

Just post your answers comments. If you are reading this in RSS or email, then click here to post your answer.

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

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

212 Responses to “Calculate maximum change [homework]”

  1. Amit says:

    Answer to Question

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

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

  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

  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:

    Hi, Please edit the formula to d3:d7 instead of d87 inadvertantly posted

  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

  13. Eamon says:

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

    https://docs.google.com/spreadsheets/d/1uJI5knArPJWgnBZrKrbmo5RBnil59-UguKD423n-Hzk/edit?usp=sharing

    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:

    The answer is 26.32%.

    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:

    clearly, array formula good answer.

    [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)}

  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)}

  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))}

  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))

    (Press: Ctrl+Shift+Enter instead of: Enter)

    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))}

  68. PavanSada says:

    {=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))}

  71. PavanSada says:

    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))}
    resultado é 33%

  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)

  89. Mukundan says:

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

  90. 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)

  91. 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)

  92. 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.

  93. Punet says:

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

  94. Johnson Mathias says:

    all above formulas are entered by Ctrl Shift Enter

  95. 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.

  96. Yatin says:

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

  97. SAMBIT KUMAR MOHAPATRA says:

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

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

  98. 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

  99. RobertThi says:

    =MAX(C2:D8)

  100. javhkhlantugs says:

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

  101. 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”

  102. John Kragh says:

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

  103. 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

  104. Naveen BP says:

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

  105. 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))

  106. hassan says:

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

  107. Alexis says:

    =MAX(ABS($D-$C)

  108. Ankit Chopra says:

    =MAX(K3:K8-J3:J8)

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

    Answer is 25

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

  109. 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)}

  110. Yogesh says:

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

  111. Rupesh Kumar says:

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

  112. Frank Bernard says:

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

  113. 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

  114. Emil Lascau says:

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

  115. Viraj Chachad says:

    Hi Chando

    I used this formula:

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

    Thanks

    Viraj

  116. Alok Kumar Jena says:

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

    Enter Ctrl+Shift+Enter

  117. Sheela A.M says:

    =Max()

  118. 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]

  119. hooroy says:

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

  120. Anil Arora says:

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

  121. Rishab says:

    25
    Product 6

  122. Nik says:

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

  123. 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))}

  124. Chus says:

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

  125. Henk Stander says:

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

  126. 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)}

  127. 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))))}

  128. 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.

  129. C.Treffner says:

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

  130. 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)

  131. Pablo says:

    Hello
    An array formula will do the trick:

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

    showing 25, which is the right answer

  132. 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

  133. Chris says:

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

    Regards

    Chris

  134. 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)))}

  135. Hanlie Croeser says:

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

  136. JanWim says:

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

  137. Jerry Giles says:

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

  138. 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.

  139. 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)}

  140. 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))}

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

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

  143. Rudra Sharma says:

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

    With Regards
    Rudra

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

  145. aditya says:

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

  146. 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))}

  147. Vivek says:

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

  148. Ben Mosbeh Mohamed Slim says:

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

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

  150. PSG says:

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

    Press C+S+E (Array Function)

  151. Daffy says:

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

  152. 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))}

  153. Mustafa says:

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

  154. 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)

  155. Ashok says:

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

  156. 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)

  157. marvin says:

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

  158. Vivek S says:

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

  159. 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.

  160. Md. Nazmul Muneer says:

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

  161. 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)

  162. Maria says:

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

  163. 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))

  164. 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!

  165. Koel Ray says:

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

  166. 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 :)

  167. 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)}

  168. Ajay Rajguru says:

    Question: Which product is responsible for this change?
    Answer: write the following formula;

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

  169. 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

  170. John Fuller says:

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

  171. Suresh TNVB says:

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

  172. Jo says:

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

Leave a Reply