Excel file (program)

Here we have a weekly time series: each week, a value. For each value, we calculate the 1, 4, 13 e 26 week rate of change. At this point, we need to compare this data with all previous data (historical weekly data goes back to 2004) in order to find and sort all years similar. So we need to define a standard to quantify the deviation of historical data (4 weekly RoCs) with current one. I developed a sheet where I try this. Clearly, we need to block last values, 'cause each previous row needs to be compared with always the latter one. A rather time consuming task, should this routine be done on a weekly basis. I repeat: we need to find the group of data (1w, 4w, 13w and 26w RoC) most similar to the latest one. Before that, we need to gauge how much each previous data was near/far from latest one. Once measured the deviation in absolute term, we need to relativize it by calculating a percentile value: the difference, in absolute value, need to be referenced to the highest/lowest historical value. Then we can compare apples with apples... After calculating, for each date, the relative distance with latest data, we could add the four deviation in order to get a score. Like golf, the weeks with lowest handicap (pardon: cumulative distance) are the most similar to latest one. At this point, we can take the dates, the final score; then we can sort data from lowest to highest, getting the ten most similar row to the latest one. At this point, we could plot a line chart, showing the average/median value for this cluster of data, 26 weeks before and after the date chosen. Then, we can add the latest 26 weeks of data, wishing that it compares favorably with average/mean value, and hoping that future data will align with the average/mean data. Obviously, in order to calculate an average, we need to deal with homogeneous data: the values need to be inxeded, by putting equal to "100" the data corresponding to the date selected. This task can be done manually. It requires some time. Above all, since there are many absolute reference, the following week is not possible to copy the formulas in the newer row: you need to rewrite them by moving the absolute reference, and than copy and pasting the newer formulas backward. Moreover, other ten "best" dates will be selected, and another chart will born... My purpose is to obtain an Excel file that, by just inserting the latest value, will do all the work for me, providing the line chart showing the "best path". A look at the Excel file I developed will help to better understand my needs.