comparing many cols of data to one col
 

I have many sets of percentages (can over 20) for six month periods that I
analyzed. When I get a new set of percentages, I want to compare it to the
old sets and see which old set it is most similar to. I have been doing this
by lines graph but is there an easier way because I have alot of old sets.
See below

MON DATA 1 DATA 2 DATA 3 €¦DATA 20 NEW DATA
6 7.16% 5.94% 11.89% 8.08%
5 11.86% 7.52% 11.36% 11.56%
4 20.15% 13.27% 14.91% 25.01%
3 25.89% 23.25% 21.03% 26.73%
2 6.59% 24.01% 8.70% 13.41%
1 28.34% 26.03% 32.12% 15.21%

comparing many cols of data to one col
 

You could use a least squares difference approach. This sums the square of
the differences between the corrresponding values for each month across each
data set. Assume your old data sets are in columns B,C,D and new one in E -
rows 20 to 25. Place the following formula in another row in column B and
drag/copy across to column D.

=SUM(((B20:B25)-($E$20:$E$25))^2)

The lowest number should represent the closest fit, according to the least
squares criterion. There may be better statistical measures of goodness of
fit that might discard extreme outliers or other anomalies, etc.

You may also need to modify the formula to easily acxcomodate adding
additional data readings ...

"gb" wrote:

I have many sets of percentages (can over 20) for six month periods that I
analyzed. When I get a new set of percentages, I want to compare it to the
old sets and see which old set it is most similar to. I have been doing this
by lines graph but is there an easier way because I have alot of old sets.
See below

MON DATA 1 DATA 2 DATA 3 €¦DATA 20 NEW DATA
6 7.16% 5.94% 11.89% 8.08%
5 11.86% 7.52% 11.36% 11.56%
4 20.15% 13.27% 14.91% 25.01%
3 25.89% 23.25% 21.03% 26.73%
2 6.59% 24.01% 8.70% 13.41%
1 28.34% 26.03% 32.12% 15.21%

comparing many cols of data to one col
 

OOOPS! I should have added that the formula should be confirmed with
ctl-shift-enter, since it is an array formula ...

"gb" wrote:

I have many sets of percentages (can over 20) for six month periods that I
analyzed. When I get a new set of percentages, I want to compare it to the
old sets and see which old set it is most similar to. I have been doing this
by lines graph but is there an easier way because I have alot of old sets.
See below

MON DATA 1 DATA 2 DATA 3 €¦DATA 20 NEW DATA
6 7.16% 5.94% 11.89% 8.08%
5 11.86% 7.52% 11.36% 11.56%
4 20.15% 13.27% 14.91% 25.01%
3 25.89% 23.25% 21.03% 26.73%
2 6.59% 24.01% 8.70% 13.41%
1 28.34% 26.03% 32.12% 15.21%

comparing many cols of data to one col
 

I have never used an array - when do you type ctl-shift-enter ??
Thankss for all your help, gb

"DOR" wrote:

OOOPS! I should have added that the formula should be confirmed with
ctl-shift-enter, since it is an array formula ...

"gb" wrote:

I have many sets of percentages (can over 20) for six month periods that I
analyzed. When I get a new set of percentages, I want to compare it to the
old sets and see which old set it is most similar to. I have been doing this
by lines graph but is there an easier way because I have alot of old sets.
See below

MON DATA 1 DATA 2 DATA 3 €¦DATA 20 NEW DATA
6 7.16% 5.94% 11.89% 8.08%
5 11.86% 7.52% 11.36% 11.56%
4 20.15% 13.27% 14.91% 25.01%
3 25.89% 23.25% 21.03% 26.73%
2 6.59% 24.01% 8.70% 13.41%
1 28.34% 26.03% 32.12% 15.21%

comparing many cols of data to one col
 

Type or copy the formula into the first cell then, instead of pressing enter,
press and hold Ctl-shift and press enter. That will "enter" the formula as
an array formula - you will see curly brackets around it. Now copy/drag the
formula to the other cells.


"gb" wrote:

I have never used an array - when do you type ctl-shift-enter ??
Thankss for all your help, gb

"DOR" wrote:

OOOPS! I should have added that the formula should be confirmed with
ctl-shift-enter, since it is an array formula ...

"gb" wrote:

I have many sets of percentages (can over 20) for six month periods that I
analyzed. When I get a new set of percentages, I want to compare it to the
old sets and see which old set it is most similar to. I have been doing this
by lines graph but is there an easier way because I have alot of old sets.
See below

MON DATA 1 DATA 2 DATA 3 €¦DATA 20 NEW DATA
6 7.16% 5.94% 11.89% 8.08%
5 11.86% 7.52% 11.36% 11.56%
4 20.15% 13.27% 14.91% 25.01%
3 25.89% 23.25% 21.03% 26.73%
2 6.59% 24.01% 8.70% 13.41%
1 28.34% 26.03% 32.12% 15.21%

comparing many cols of data to one col
 

Thanks for all your help !! It worked !!

gb

"DOR" wrote:

Type or copy the formula into the first cell then, instead of pressing enter,
press and hold Ctl-shift and press enter. That will "enter" the formula as
an array formula - you will see curly brackets around it. Now copy/drag the
formula to the other cells.


"gb" wrote:

I have never used an array - when do you type ctl-shift-enter ??
Thankss for all your help, gb

"DOR" wrote:

OOOPS! I should have added that the formula should be confirmed with
ctl-shift-enter, since it is an array formula ...

"gb" wrote:

I have many sets of percentages (can over 20) for six month periods that I
analyzed. When I get a new set of percentages, I want to compare it to the
old sets and see which old set it is most similar to. I have been doing this
by lines graph but is there an easier way because I have alot of old sets.
See below

MON DATA 1 DATA 2 DATA 3 €¦DATA 20 NEW DATA
6 7.16% 5.94% 11.89% 8.08%
5 11.86% 7.52% 11.36% 11.56%
4 20.15% 13.27% 14.91% 25.01%
3 25.89% 23.25% 21.03% 26.73%
2 6.59% 24.01% 8.70% 13.41%
1 28.34% 26.03% 32.12% 15.21%