'Indexing' (?) time series

[sdspieg]

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	Country	1990	1991	1992	1993	1994	1995	1996	1997	1998	1999	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019
2	China	0.000003	0.000003	0.000001		0.000005	0.000002	0.000007	0.000006	0.000006	0.000005	0.000014	0.00001	0.000011	0.000015	0.00001	0.000013	0.000013	0.000015	0.000014	0.000019	0.00002	0.00002	0.000018	0.000029	0.000033	0.000047	0.000041	0.000027	0.000017	0.00001
3	Russia	0.000003	0.000001		0.000003	0.000005	0.000006	0.000005	0.000009	0.000005	0.000003	0.000012	0.000006	0.000005	0.000009	0.000006	0.000009	0.00001	0.000013	0.000013	0.000017	0.000015	0.000012	0.000014	0.000009	0.000024	0.000035	0.000035	0.000018	0.000023	0.000013
4	United States	0.000009	0.000016	0.000017	0.000008	0.000016	0.000017	0.000015	0.000027	0.000017	0.000016	0.000018	0.000022	0.000026	0.00004	0.000033	0.000027	0.000026	0.000032	0.000033	0.000029	0.000033	0.000028	0.000031	0.000031	0.000027	0.000037	0.000045	0.000022	0.000017	0.000011

<tbody>
</tbody>

Sheet3​

These are data for 3 countries over 30 years. They data were normalized based on a much larger dataset. That is why the values are so low. But so to show change over time we'd like to renormalize/index (not sure what the proper term is) these data with the country-year that has the highest value in THIS dataset displayed as '100', and all others as fractions thereof. So in this case, =MAX(R2:AE4) is 0.000047 (AA2, or China in 2015). Is there a formula that can adjust all of these values proportional to that value? I see here how to do that when the MAX is in the first column/first row with data. But how to do sthg similar when the 'base' value that should become 100 is in another cell, and you want to get the ''right' values in all cells both to the left AND the right?

So visually - in this picture, the highest 'peak' across all datasets would get '100' on the y-axis, and all other data points would get fractions thereof.

And also what is the proper term of this transformation? Thanks!

 

[AlphaFrog]

<br />

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	Country	1990	1991	1992	1993	1994	1995	1996	1997	1998	1999	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019
2	China	0.000003	0.000003	0.000001		0.000005	0.000002	0.000007	0.000006	0.000006	0.000005	0.000014	0.00001	0.000011	0.000015	0.00001	0.000013	0.000013	0.000015	0.000014	0.000019	0.00002	0.00002	0.000018	0.000029	0.000033	0.000047	0.000041	0.000027	0.000017	0.00001
3	Russia	0.000003	0.000001		0.000003	0.000005	0.000006	0.000005	0.000009	0.000005	0.000003	0.000012	0.000006	0.000005	0.000009	0.000006	0.000009	0.00001	0.000013	0.000013	0.000017	0.000015	0.000012	0.000014	0.000009	0.000024	0.000035	0.000035	0.000018	0.000023	0.000013
4	United States	0.000009	0.000016	0.000017	0.000008	0.000016	0.000017	0.000015	0.000027	0.000017	0.000016	0.000018	0.000022	0.000026	0.00004	0.000033	0.000027	0.000026	0.000032	0.000033	0.000029	0.000033	0.000028	0.000031	0.000031	0.000027	0.000037	0.000045	0.000022	0.000017	0.000011
5
6	REINDEXED
7	China	6.382979	6.382979	2.12766	0	10.6383	4.255319	14.89362	12.76596	12.76596	10.6383	29.78723	21.2766	23.40426	31.91489	21.2766	27.65957	27.65957	31.91489	29.78723	40.42553	42.55319	42.55319	38.29787	61.70213	70.21277	100	87.23404	57.44681	36.17021	21.2766
8	Russia	6.382979	2.12766	0	6.382979	10.6383	12.76596	10.6383	19.14894	10.6383	6.382979	25.53191	12.76596	10.6383	19.14894	12.76596	19.14894	21.2766	27.65957	27.65957	36.17021	31.91489	25.53191	29.78723	19.14894	51.06383	74.46809	74.46809	38.29787	48.93617	27.65957
9	United States	19.14894	34.04255	36.17021	17.02128	34.04255	36.17021	31.91489	57.44681	36.17021	34.04255	38.29787	46.80851	55.31915	85.10638	70.21277	57.44681	55.31915	68.08511	70.21277	61.70213	70.21277	59.57447	65.95745	65.95745	57.44681	78.7234	95.74468	46.80851	36.17021	23.40426
Sheet1

Cell Formulas
Range	Formula
B7	=B2/MAX($B$2:$AE$4)*100

 

[sdspieg]

The MrExcel magic strikes again!!! Thanks much AlphaFrog!