Compare a column with different columns and find the most similar column to it

I have a data of 12 years from 2008-19. I want to compare 2019 with all the years and find which is the most 'similar' year to it statistically.

i am not sure, what is your criteria. As per my assuming.
In "M12"

Formula:

Please Login or Register  to view this content.

Ensure simultaneously pressing shift+ctrl+enter
N12 is criteria value. [In above formula you can change MIN to MAX also.
See the attachment file.

Originally Posted by Sarangsood

I have a data of 12 years from 2008-19. I want to compare 2019 with all the years and find which is the most 'similar' year to it statistically and find the values for Oct'19, Nov'19, Dec'19 accordingly by comparing it with the similar year.

As per my understanding, you want to compare datasets to see if they are different statistically.
T-test can come handy...
Calculate Mean, STDEV and then perform T-Test.
You will see that data from 2016 and 2018 are significantly similar to 2019 upto September (T-Value >5%).
You know the data better than any of us here so, it's your call whether to pull data from 2016 or 2018.
Use INDEX/MATCH to pull the data based on T-Value.

Apologies if I didn't get your problem right.

I am not sure of criteria either. I am also not too 'swift' when it come to statistics. I took some guesses and used standard deviations and from there FORECAST.

Two approaches. You may have to array enter the final formulas. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Approach 1:
Uses helper row 6 with STDEV.P for each column's data.

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

Then finds the absolute differences of the first 11 from last, matches the MINIMUM of those to the differences and INDEXes the headers based upon that match. In N6.

Formula:

Please Login or Register  to view this content.

Approach 2 does the same as 1 but is contained in a one off formula. In N7

Formula:

Please Login or Register  to view this content.

Then in either event the forecast numbers in M17:M19. Entered in M17 and filled down.

Formula:

Please Login or Register  to view this content.

Similar would mean that if i draw a line graph of all the years, i want to find the most similar looking. I'm not able to categorize it into exactly which statistical formula will it fit into.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

i want to find which is the most similar year to 2019

In trying to find the closest fit to a given line I believe that R² is what you want.
Similar to what Dave set up in post #6 cells B6:M6 are populated using: =RSQ(B8:B16,$M8:$M16)
Cell N6 displays the year matching the highest R² value using: =INDEX(B7:L7,MATCH(MAX(B6:L6),B6:L6,0))
The first sentence of the article linked below may help explain why I feel that R² is the best measure for your purposes.
https://www.techwalla.com/articles/h...ulate-r2-excel
Let us know if you have any questions.

Originally Posted by JeteMc

. . . closest fit to a given line I believe that R² is what you want. . . .

R² is akin to DEVSQ/SSE (sum of squared errors). A straight line can be a closer match for a parabola near the parabola's extreme point, but other parabolas would be much closer to the same shape. Even just considering 2 straight lines, one line with negative slope could be closer to another line with positive slope within some range than another line with positive slope. OP has mentioned closeness of graphs, which strikes me as caring at least as much about shape as local proximity.

ADDED: R² could be useful, but in a different sense. It could be used to decide whether linear, exponential, polynomial, exponentiated polynomial or mixed curve fits best represented each column, then select closest from among the curves of the same general shape.