Biggest change occurs in which year?

[Waimea]

Hi, I am trying to check a table/range for what the maximum change in percent is and to get the year, ex. 2019, in which the biggest change occurs in.

I am tying to use MAX and MATCH formulas w/o success. In my dataset I have more years and more values but it seems fitting to post 3 values here.

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64, align: right"]2017[/TD]
[TD="width: 64, align: right"]2018[/TD]
[TD="width: 64, align: right"]2019[/TD]
[/TR]
[TR]
[TD="align: right"]0,196[/TD]
[TD="align: right"]0,199[/TD]
[TD="align: right"]0,203[/TD]
[/TR]
</tbody>[/TABLE]

In the above example I would like to get the following output:

The biggest change occurs in year 2019 with 20,3...%.


Also, if possible I would like to rank the years from largest to smallest or vice versa, in my dataset I have more years and more values.

Year Rank
2017 3
2018 2
2019 1

 

[Peter_SSs]

In the above example I would like to get the following output:

The biggest change occurs in year 2019 with 20,3...%.

What output would you want for this sample data?

Excel 2016
	A	B	C
1	2017	2018	2019
2	0.203	0.199	0.203
Sample

 

[Waimea]

Thank you for your reply,

In my dataset there are more decimals so the possiblility of the same values ocurring are very small.

However, from your sample data I would like the following output:

"The biggest change occurs in year 2017 with 20.3% and in year 2019 with 20,3%. "

 

[Toadstool]

Hi Waimea,

For the columns across I've allowed until J. You can of course change he J2 references to any value through XFD2.

The row 5 formulae are to be copied down as far as necessary.

If two percentages are the same you will see two rows with the same percentage and same ranking but the next below will skip a rank.

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2019[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]0.196[/TD]
[TD="align: center"]0.199[/TD]
[TD="align: center"]0.203[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]Rank[/TD]
[TD="align: center"]Value[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]2019[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0.203[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0.199[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0.196[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A5[/TH]
[TD="align: left"]=INDEX($A$1:$J$1,AGGREGATE(15,6,COLUMN($A$2:$J$2)/($A$2:$J$2=C5),COUNTIF($C5:$C$18,C5)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=RANK.EQ(C5,$A$2:$J$2,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5
[/TH]
[TD="align: left"]=AGGREGATE(14,6,$A$2:$J$2,ROWS($A$4:A4))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

See if either of these is any use. If your version of Excel does not have the TEXTJOIN function and you say you have more years than 3, then I don't think this is feasible with ordinary worksheet functions and you would need to move to a vba solution. So, if you don't have TEXTJOIN, do you want a vba suggestion?

Both these formulas are array formulas so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formulas can then be copied down.

Excel Workbook
	A	B	C	D	E	F
1	2017	2018	2019
2	0.196	0.199	0.203		The biggest change occurs in year 2019 with 20.3%	The biggest change occurs in year 2019 with 20.3%
3	0.012	0.5	0.23		The biggest change occurs in year 2018 with 50.0%	The biggest change occurs in year 2018 with 50.0%
4	0.203	0.199	0.203		The biggest change occurs in year 2017 & 2019 with 20.3%	The biggest change occurs in year 2017 with 20.3% and in year 2019 with 20.3%
Biggest Change

 

[Waimea]

Thank you Peter_SSs and Toadstool for your replies!

I am going to try both approaches!

I will make a post when I have it working!

 

[Peter_SSs]

I would recommend one change to Toadstool's A5 formula. Without the change, if somebody was to subsequently insert any new columns to the left of the sheet, it would then return incorrect results.

=INDEX($A$1:$J$1,AGGREGATE(15,6,(COLUMN($A$2:$J$2)-COLUMN($A2)+1)/($A$2:$J$2=C5),COUNTIF($C5:$C$18,C5)))

 

[Waimea]

Thank you again for your replies, I got both working and I think that I'll use both of your formulas! One for the percentage change and one with the ranking.

However, I get something wrong with my formatting of the percentage.

[TABLE="width: 704"]
<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]2012[/TD]
[TD="width: 64, align: right"]2013[/TD]
[TD="width: 64, align: right"]2014[/TD]
[TD="width: 64, align: right"]2015[/TD]
[TD="width: 64, align: right"]2016[/TD]
[TD="width: 64, align: right"]2017[/TD]
[TD="width: 64, align: right"]2018[/TD]
[TD="width: 64, align: right"]2019[/TD]
[TD="width: 64, align: right"]2020[/TD]
[TD="width: 64, align: right"]2021[/TD]
[TD="width: 64, align: right"]2022[/TD]
[/TR]
[TR]
[TD="class: xl131, align: right"]17,19%[/TD]
[TD="class: xl131, align: right"]17,85%[/TD]
[TD="class: xl131, align: right"]18,41%[/TD]
[TD="class: xl131, align: right"]18,88%[/TD]
[TD="class: xl131, align: right"]19,27%[/TD]
[TD="class: xl131, align: right"]19,64%[/TD]
[TD="class: xl131, align: right"]19,98%[/TD]
[TD="class: xl131, align: right"]20,33%[/TD]
[TD="class: xl131, align: right"]20,57%[/TD]
[TD="class: xl131, align: right"]20,89%[/TD]
[TD="class: xl131, align: right"]21,21%[/TD]
[/TR]
</tbody>[/TABLE]

This sample produces the following text:

"The biggest change occurs in year 2022 with 2.1%"

I want the 2,1% to be 21,21%.

What am I doing wrong?

 

[Waimea]

I got it to work, I didn't change the . to a , in the format formula.

Thank you again for your input and your help!

 

[Peter_SSs]

I got it to work, I didn't change the . to a , in the format formula.

Thank you again for your input and your help!

Yes, I forgot to mention the different decimal indicator. Glad you figured it out anyway.