Finding a Location Value

[broncos347]

I have a spreadsheet which shows a list of locations against a number of years which show a percentage decrease across the years as shown below.

Book1
	A	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Station Name	2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030
3	Acle Stn	0%	-4%	-8%	-12%	-16%	-20%	-24%	-28%	-32%	-36%	-40%	-44%	-48%
4	Alresford Stn	92%	88%	84%	80%	76%	72%	68%	64%	60%	56%	52%	48%	44%
5	Althorne Stn	92%	88%	84%	80%	76%	72%	68%	64%	60%	56%	52%	48%	44%
6	Angel Road Stn	96%	92%	88%	84%	80%	76%	72%	68%	64%	60%	56%	52%	48%
7	Attleborough Stn	40%	36%	32%	28%	24%	20%	16%	12%	8%	4%	0%	-4%	-8%
8	Audley End Stn	84%	80%	76%	72%	68%	64%	60%	56%	52%	48%	44%	40%	36%
9	Battlesbridge Stn	40%	36%	32%	28%	24%	20%	16%	12%	8%	4%	0%	-4%	-8%
10	Beccles Stn	20%	16%	12%	8%	4%	0%	-4%	-8%	-12%	-16%	-20%	-24%	-28%
11	Berney Arms Stn	96%	92%	88%	84%	80%	76%	72%	68%	64%	60%	56%	52%	48%
12	Billericay Stn	60%	56%	52%	48%	44%	40%	36%	32%	28%	24%	20%	16%	12%
13	Bishops Stortford Stn	-20%	-24%	-28%	-32%	-36%	-40%	-44%	-48%	-52%	-56%	-60%	-64%	-68%
14	Braintree Freeport Stn	96%	92%	88%	84%	80%	76%	72%	68%	64%	60%	56%	52%	48%
15	Braintree Stn	60%	56%	52%	48%	44%	40%	36%	32%	28%	24%	20%	16%	12%
16	Brampton (Suffolk) Stn	0%	-4%	-8%	-12%	-16%	-20%	-24%	-28%	-32%	-36%	-40%	-44%	-48%
17	Brandon Stn	92%	88%	84%	80%	76%	72%	68%	64%	60%	56%	52%	48%	44%
18	Brimsdown Stn	20%	16%	12%	8%	4%	0%	-4%	-8%	-12%	-16%	-20%	-24%	-28%
19	Broxbourne Stn	60%	56%	52%	48%	44%	40%	36%	32%	28%	24%	20%	16%	12%
Sheet1

What I would like to be able to do is to show which year the percentage decrease reaches 16% for each location. Can anyone help?

 

[Gerald Higgins]

Just to be clear, in your sample data, please point out exactly which entries match your criteria.

For example, is it 2024 for Attleborough, when it gets to 16% ?
Or is it 2027 for Beccles, when it gets to -16% ?
Or maybe 2019 for Beccles, when it gets to 16% ?

And how do you want to "show which year" ?
Maybe colour coding of the data itself using Conditional Formating ?
Or something else ?

 

[broncos347]

Gerald, thanks for the input, apologies I should have put some more info in my original post, to answer your query i am looking for the figure when it reaches 16% so it would be 2024 for Attleborough and 2019 for Beccles as you suggest.

As to how do I want to show the year, I would like to be able to show the year as a number in an additional column say column B.

I hope that make more sense.

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Station Name				2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030
2	Acle Stn				0%	-4%	-8%	-12%	-16%	-20%	-24%	-28%	-32%	-36%	-40%	-44%	-48%
3	Alresford Stn				92%	88%	84%	80%	76%	72%	68%	64%	60%	56%	52%	48%	44%
4	Althorne Stn				92%	88%	84%	80%	76%	72%	68%	64%	60%	56%	52%	48%	44%
5	Angel Road Stn				96%	92%	88%	84%	80%	76%	72%	68%	64%	60%	56%	52%	48%
6	Attleborough Stn	2024			40%	36%	32%	28%	24%	20%	16%	12%	8%	4%	0%	-4%	-8%
7	Audley End Stn				84%	80%	76%	72%	68%	64%	60%	56%	52%	48%	44%	40%	36%
8	Battlesbridge Stn	2024			40%	36%	32%	28%	24%	20%	16%	12%	8%	4%	0%	-4%	-8%
9	Beccles Stn	2019			20%	16%	12%	8%	4%	0%	-4%	-8%	-12%	-16%	-20%	-24%	-28%
10	Berney Arms Stn				96%	92%	88%	84%	80%	76%	72%	68%	64%	60%	56%	52%	48%
11	Billericay Stn	2029			60%	56%	52%	48%	44%	40%	36%	32%	28%	24%	20%	16%	12%
Sheet2

Cell Formulas
Range	Formula
B2	=IFERROR(INDEX($E$1:$Q$1,MATCH(0.16,E2:Q2,0)),"")

 

[broncos347]

works a treat, many thanks Fluff.

 

[Fluff]

You're welcome & thanks for the feedback