Menu
Calculate maximum change [homework]
Formula Challenges - 224 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?
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 < and > 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.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Free Invoice Template using Excel – Download | Why you should close down Excel completely » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
224 Responses to “Calculate maximum change [homework]”
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))}
@Amit,
Did you mean: {=MAX(IFERROR(1-(C1:C6/B1:B6),))}
Your solution return the maximum positive change not the maximum change. The maximum change occurs in Product 2 with a negative 33% change.
@Kerry,
Whom do you address with your reply ?
It is customary to specify the name.
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
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))}
Not to my opinion...
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))}
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))}
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
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?
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.
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.
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..
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
Hi, Please edit the formula to d3:d7 instead of d87 inadvertantly posted
{=max(abs(c3:c8-d3:d8))}
the difference in % for the product 2 is -33.3. hence the max change
{=MAX(ABS(C3:C8-D3:D8))}
// Its an array function
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))}
@Eamon,
Would you be so kind to share with us the "bis secret" U R holding in cell C16 !?
Michael (Micky) Avidan
Sorry,
C16 is the returned value from {=MAX(C2:C7)-(B2:B7))}.
@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
Thanks @Michael (Micky) Avidan.
That worked.
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?
Spoke too soon - it gives an answer - but the wrong answer!!!
=AGGREGATE(14,4,ABS((This.Month)-(Last.Month)),1)
Not array entered
or
=AGGREGATE(14,4,ABS(MMULT(C3:D8,{-1;1})),1)
not array entered
+1 for clever use of mmult()
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.
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))
@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
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
Andrew,
You don't like array formulas, but those are array formulas. You just don't confirm them with Ctrl+Shift+Enter
Regards
@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
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.
you formula are incorrect
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.
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)}
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)}
easy and usefull 🙂
{=MAX((B1:B6-A1:A6))}
{=MAX($B$2:$B$7-$C$2:$C$7)}
={Max(Abs([this Month]-[lastMonth]))}
Use
{=max (d3:d8-c3:c8)}
{=MAX((C4:C8)-(C3:C7))}
={MAX(C1:C6/B1:B6)}
=(dmax(c3.c8)-dmax(d3.d8))
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
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.
=MAX(ABS(C3:C8-D3:D8))
=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)
=MAX(E2:E7) = 26.32
Where e2 to e7 contain forumla for % ? eg =(D2/C2-1)*100
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
={MAX(((C2:C7)-(B2:B7))/(B2:B7))}
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
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.
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)
Hello,
{=MAX(D3:D8-C3:C8)}
Ctrl + Shift + Enter
Jean-Eric from France
={SUM(MAX(ABS($D$3:$D$8-$E$3:$E$8)))}
=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
{=MAX(C2:C7-B2:B7)} - Array Formula
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
{=MAX(ABS(B2:B7-C2:C7))}
=MAX((C3-D3),(C4-D4),(C5-D5),(C6-D6),(C7-D7),(C8-D8))
Product 2
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%.
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
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
{=MÁXIMO(ABS(D3:D8-C3:C8))}
=MAXA(B3:C3,B4:C4,B5:C5,B6:C6,B7:C7,B8:C8)
=MAXA(B3:C3,B4:C4,B5:C5,B6:C6,B7:C7,B8:C8)
{=MAX(B2:B7-C2:C7)}
2 lesson of the book Ctrl + Shift + Enter
=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)}
...(ctrl alt enter) instead of enter -- sorry, this got cut off from my message above.
...sorry again, it is (ctrl shift enter)...how do you edit posts here?
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.
{=INDEX(B3:B8,MATCH(MAX(ABS((C3:C8-D3:D8)/C3:C8)),ABS((C3:C8-D3:D8)/C3:C8),0))}
{=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.
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))
{=max(abs(D3:D8-C3:C8))}
Max change = product 2 at 40 units
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).
{=MAX(B1:B6-A1:A6)}
it is {=MAX(D1:D6-C1:C6)} where curly brackets comes automatically when we press CTRL+SHIFT+Enter
{=IF(ABS(MIN(C3:C8-B3:B8))>MAX(C3:C8-B3:B8),MIN(C3:C8-B3:B8),MAX(C3:C8-B3:B8))}
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))}
{=IF(ABS(MIN(D3:D8-C3:C8))>MAX(D3:D8-C3:C8),MIN(D3:D8-C3:C8),MAX(D3:D8-C3:C8))}
Uses "> for >"
Bonus:
{=INDEX($B$3:$B$8,MATCH(IF(ABS(MIN(D3:D8-C3:C8))>MAX(D3:D8-C3:C8),MIN(D3:D8-C3:C8),MAX(D3:D8-C3:C8)),D3:D8-C3:C8,0))}
Uses ">" for >
=MAX(B2:B7-C2:C7)
then Ctrl + Shift + Enter in the cell
Array formula:
=MAX(($C$3:$C$8)-($D$3:$D$8))
(Press: Ctrl+Shift+Enter instead of: Enter)
result=40
{=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 😉
{=MAX(C2:C7-D2:D7,D2:D7-C2:C7)}
{=MAX(ABS(C3:C8-D3:D8))}
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)
=MAX(C4-D4,C5-D5,C6-D6,C7-D7,C8-D8,C9-D9)
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)}
{=MAX(ABS((C3:C8-B3:B8)/B3:B8))}
Note: Array Formula
{=MAX(ABS((C3:C8-B3:B8)/B3:B8))}
Confirmed by Cntrl Shit Entre
{=MAX(ABS(C2:C8)-ABS(D2:D8))}
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)))}
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
=Max(C3:C8-D3:D8)
entered as an array formula
{=INDEX(J3:L8,MATCH(MAX(K3:K8-L3:L8),K3:K8-L3:L8,0),1)}
Bem a resposta é
{=MÁXIMO(ABS((D3:D8-C3:C8)/C3:C8))}
resultado é 33%
=(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.
{=MAX(ABS(I5:I10-J5:J10))}
Sorry, I put data in rows I & J instead of C & D.
Hi Chandoo,
Here is my solution ..
=MAX(C3:C8-B3:B8) CTRL+SHIF+ENTER
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
{=MAX(C3:C8-D3:D8)}
{=MAX(ABS(C3:C8-D3:D8))}
=(max(c3:c8)-min(c3:c8))
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.
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))
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)
{=Max(d3:d8-c3:c8)}
{=MAX(C3:C8-D3:D8)}
=max(C1:c8-d1:d8)
cntrl+shift+enter
no
=max(large($C$2:$C$8,1),large($d$2:$d$8,1))
=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)
=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
=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
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)
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.
=Max(d3:d8-c3:c8) - array formula
all above formulas are entered by Ctrl Shift Enter
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.
=MAX(ABS(F4:F9-E4:E9))
=MAX(ABS(C5:C10-B5:B10))
Press (CTRL+SHIFT+ENTER) KEY instead of ENTER KEY
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
=MAX(C2:D8)
=LARGE(ABS(D3:D8-C3:C8),1)
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"
=MAKSV(ABS(B3-C3);ABS(B4-C4);ABS(B5-C5);ABS(B6-C6);ABS(B7-C7);ABS(B8-C8)) = 40
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
{=MAX(ABS(E9:E14-F9:F14))}
retursns 40
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))
{=MAX(B1:B6-C1:C6)}
=MAX(ABS($D-$C)
=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)
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.
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)}
First: =MAX(ABS((C2:C7)-(D2:D7)))Second:=INDEX(B2:B7,MATCH(C16,ABS((C2:C7)-(D2:D7)),0))
We can calculate maximum change by using the array formula.
{=Max(C3:C8-D3:D8)}
With your help (how to enter a matrix formula)
{=MAX(ABS(D3:D8-C3:C8))}
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
=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
=MAX((ABS(D2:D7-C2:C7)))
Enter Ctrl+Shift+Enter
=Max()
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]
Challenge #1 solution:
{=MAX(ABS(C3:C8-D3:D8))}
Array entered
Product 6
{=MAX(ABS((C2:C7-B2:B7)/B2:B7))}
25
Product 6
Correct answer is 40. product 2
=max(abs(c3-d3),abs(c4-d4),abs(c5-d5),abs(c6-d6),abs(c7-d7),abs(c8-d8))
={LARGE(ABS((D3:D8)-(C3:C8)),1)}
={INDEX((B3:B8),MATCH(LARGE((C3:C8)-(D3:D8),1),(C3:C8)-(D3:D8),0))}
={MAX((C2:C5)/(B2:B5)-1)}
'{=MAX(ABS((D3:D8)-(C3:C8)))}
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)}
{=MAX((B3:B8)/(A3:A8)-1)}
Sorry - the last result was based on my own workbook.
based on Chandoo's version, below the result
{=MAX((D3:D8)/(C3:C8)-1)}
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))))}
=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.
{=MAX(ABS(C3:C8-D3:D8))}
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)
Hello
An array formula will do the trick:
{=MAX(C3:C8-B3:B8)}
showing 25, which is the right answer
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
My formula entered as an array
=LARGE(D2:D7-C2:C7,1)
Regards
Chris
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)))}
{=MAX(C2:C7/B2:B7-1)}
= max(c3..c8)-min(c3..c8)
=max(c3-d3,c4-d4,c5-d5,c6-d6,c7-d7,c8-d8)
{=MAX(ABS(B2:B7-C2:C7))}
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.
=max(D3:D8-C3:c8) and Press Ctrl-Shit-Enter and the formula will be change to this: {=max(D3:D8-C3:c8)}
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))}
={LARGE($D$2:$D$6-$C$2:$C$6,ROW(A1))}
={MAX(D3:D8-C3:C8)} The {} is entered by excel because it is an array function
Sorry for responding late...
My formula is
=MAX((D2:D7)-(C2:C7))
this is an array formula.
With Regards
Rudra
[…] 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.
{=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))}
={INDEX(A2:A7,MATCH(MAX(ABS(C2:C7-B2:B7)),ABS(C2:C7-B2:B7)),0)}
{=Max(ABS(C3-D3))} in absolute terme
=MAX(ABS(C3:C8-D3:D8)/C3:C8) Max rate of change
=MAX($C$2:$C$7-$B$2:$B$7)
That is for the Max positive change
=MAX(ABS((B3:B8)-(C3:C8)))
Press C+S+E (Array Function)
Max change {=MAX(ABS(C3:C8-D3:D8))}
Max change product {=INDEX(B:B,MATCH(C11,ABS(C:C-D:D),0),)}
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))}
{max(D3:D8-C3:C8)} Its an array formula, and we do that with pressing Ctrl+Shift+Enter
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)
CSE formula =MAX(D3:D8-C3:C8)
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)
CSE=MAX(ABS(C3:C8-D3:D8))
={INDEX(B3:B8,MATCH(MAX(C3:C8-D3:D8),C3:C8-D3:D8,0))}
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.
=INDEX(B3:B8,MATCH(MAX(ABS((C3:C8-D3:D8))),ABS(C3:C8-D3:D8),0))
Ctrl+Shift+Enter
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)
=MAX((C3-D3),(C4-D4),(C5-D5),(C6-D6),(C7-D7),(C8-D8))
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))
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.
hi, are you using ctr+shift+enter ? these are array formuale so you can't simply enter !
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!
=MAX( ABS(B2:B7-C2:C7))
make it an array calculation with ctrl + shift +enter
=IF(MAX(D74:D76-C74:C76)>=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 🙂
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)}
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)
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
{=MAX(ABS(C2:C7-D2:D7))}
{=MAX(B2:B7-C2:C7)}
={C1:C6-B1:B6} I think this is the simplest formula
{=MAX((L11:L16)-(M11:M16))}
={max(abs(c3:C8-D3:D8))}
{=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!
{=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%)"
pklz send me tric
=MAX(ABS(c3:c87-d3:d87))
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.
The answer is solved using Array Ctrl+Shift+Enter
{=MAX(B2:B8-A2:A8)}
Thanks
{=MAX(ABS(B2:B8-C2:C8))}