'Indexing' (?) time series

S

sdspieg

Board Regular
Joined
Dec 9, 2013
Messages
98
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
Country
China
Russia
United States

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1990[/TD]
[TD="align: right"]1991[/TD]
[TD="align: right"]1992[/TD]
[TD="align: right"]1993[/TD]
[TD="align: right"]1994[/TD]
[TD="align: right"]1995[/TD]
[TD="align: right"]1996[/TD]
[TD="align: right"]1997[/TD]
[TD="align: right"]1998[/TD]
[TD="align: right"]1999[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2001[/TD]
[TD="align: right"]2002[/TD]
[TD="align: right"]2003[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]

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

[TD="align: right"]0.000003[/TD]
[TD="align: right"]0.000003[/TD]
[TD="align: right"]0.000001[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.000005[/TD]
[TD="align: right"]0.000002[/TD]
[TD="align: right"]0.000007[/TD]
[TD="align: right"]0.000006[/TD]
[TD="align: right"]0.000006[/TD]
[TD="align: right"]0.000005[/TD]
[TD="align: right"]0.000014[/TD]
[TD="align: right"]0.00001[/TD]
[TD="align: right"]0.000011[/TD]
[TD="align: right"]0.000015[/TD]
[TD="align: right"]0.00001[/TD]
[TD="align: right"]0.000013[/TD]
[TD="align: right"]0.000013[/TD]
[TD="align: right"]0.000015[/TD]
[TD="align: right"]0.000014[/TD]
[TD="align: right"]0.000019[/TD]
[TD="align: right"]0.00002[/TD]
[TD="align: right"]0.00002[/TD]
[TD="align: right"]0.000018[/TD]
[TD="align: right"]0.000029[/TD]
[TD="align: right"]0.000033[/TD]
[TD="align: right"]0.000047[/TD]
[TD="align: right"]0.000041[/TD]
[TD="align: right"]0.000027[/TD]
[TD="align: right"]0.000017[/TD]
[TD="align: right"]0.00001[/TD]

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

[TD="align: right"]0.000003[/TD]
[TD="align: right"]0.000001[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.000003[/TD]
[TD="align: right"]0.000005[/TD]
[TD="align: right"]0.000006[/TD]
[TD="align: right"]0.000005[/TD]
[TD="align: right"]0.000009[/TD]
[TD="align: right"]0.000005[/TD]
[TD="align: right"]0.000003[/TD]
[TD="align: right"]0.000012[/TD]
[TD="align: right"]0.000006[/TD]
[TD="align: right"]0.000005[/TD]
[TD="align: right"]0.000009[/TD]
[TD="align: right"]0.000006[/TD]
[TD="align: right"]0.000009[/TD]
[TD="align: right"]0.00001[/TD]
[TD="align: right"]0.000013[/TD]
[TD="align: right"]0.000013[/TD]
[TD="align: right"]0.000017[/TD]
[TD="align: right"]0.000015[/TD]
[TD="align: right"]0.000012[/TD]
[TD="align: right"]0.000014[/TD]
[TD="align: right"]0.000009[/TD]
[TD="align: right"]0.000024[/TD]
[TD="align: right"]0.000035[/TD]
[TD="align: right"]0.000035[/TD]
[TD="align: right"]0.000018[/TD]
[TD="align: right"]0.000023[/TD]
[TD="align: right"]0.000013[/TD]

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

[TD="align: right"]0.000009[/TD]
[TD="align: right"]0.000016[/TD]
[TD="align: right"]0.000017[/TD]
[TD="align: right"]0.000008[/TD]
[TD="align: right"]0.000016[/TD]
[TD="align: right"]0.000017[/TD]
[TD="align: right"]0.000015[/TD]
[TD="align: right"]0.000027[/TD]
[TD="align: right"]0.000017[/TD]
[TD="align: right"]0.000016[/TD]
[TD="align: right"]0.000018[/TD]
[TD="align: right"]0.000022[/TD]
[TD="align: right"]0.000026[/TD]
[TD="align: right"]0.00004[/TD]
[TD="align: right"]0.000033[/TD]
[TD="align: right"]0.000027[/TD]
[TD="align: right"]0.000026[/TD]
[TD="align: right"]0.000032[/TD]
[TD="align: right"]0.000033[/TD]
[TD="align: right"]0.000029[/TD]
[TD="align: right"]0.000033[/TD]
[TD="align: right"]0.000028[/TD]
[TD="align: right"]0.000031[/TD]
[TD="align: right"]0.000031[/TD]
[TD="align: right"]0.000027[/TD]
[TD="align: right"]0.000037[/TD]
[TD="align: right"]0.000045[/TD]
[TD="align: right"]0.000022[/TD]
[TD="align: right"]0.000017[/TD]
[TD="align: right"]0.000011[/TD]

</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?

33acd1c187eae2a4e47258051f1621af-491fcf3c57d07b85a89610a15699abec-21-0.6875279178837803


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!
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
<br />
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1Country199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019
2China0.0000030.0000030.0000010.0000050.0000020.0000070.0000060.0000060.0000050.0000140.000010.0000110.0000150.000010.0000130.0000130.0000150.0000140.0000190.000020.000020.0000180.0000290.0000330.0000470.0000410.0000270.0000170.00001
3Russia0.0000030.0000010.0000030.0000050.0000060.0000050.0000090.0000050.0000030.0000120.0000060.0000050.0000090.0000060.0000090.000010.0000130.0000130.0000170.0000150.0000120.0000140.0000090.0000240.0000350.0000350.0000180.0000230.000013
4United States0.0000090.0000160.0000170.0000080.0000160.0000170.0000150.0000270.0000170.0000160.0000180.0000220.0000260.000040.0000330.0000270.0000260.0000320.0000330.0000290.0000330.0000280.0000310.0000310.0000270.0000370.0000450.0000220.0000170.000011
5
6REINDEXED
7China6.3829796.3829792.12766010.63834.25531914.8936212.7659612.7659610.638329.7872321.276623.4042631.9148921.276627.6595727.6595731.9148929.7872340.4255342.5531942.5531938.2978761.7021370.2127710087.2340457.4468136.1702121.2766
8Russia6.3829792.1276606.38297910.638312.7659610.638319.1489410.63836.38297925.5319112.7659610.638319.1489412.7659619.1489421.276627.6595727.6595736.1702131.9148925.5319129.7872319.1489451.0638374.4680974.4680938.2978748.9361727.65957
9United States19.1489434.0425536.1702117.0212834.0425536.1702131.9148957.4468136.1702134.0425538.2978746.8085155.3191585.1063870.2127757.4468155.3191568.0851170.2127761.7021370.2127759.5744765.9574565.9574557.4468178.723495.7446846.8085136.1702123.40426
Sheet1
Cell Formulas
RangeFormula
B7=B2/MAX($B$2:$AE$4)*100
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top