How to create a trendline for a series data based on another series data?

By anuar.iman ·

Here's the problem:

I have four sets of data. Let's call them;
A, B, C, and D
The data itself is all in percentages, like so:
x-axis A B C D
1 82% 117% 105% 125%
2 100% 100% 100% 100%
3 72% 87% 90% 85%
4 44% 52% 57% 45%
5 38% 43% 48% 45%
6 43% 48% 48% 45%
7 34% 39% 38% 40%
8 32% 26% 0% 35%
9 0% 0% 0% 0%

A is treated as the standard, trusted set. The other three to be compared to it.

Is there any way I can create a regression best fit line, comparing B, C, and D back to A?
At first, I thought if I click/view one series, say B, and then when I change the "Based on:" back to series A, that would do it, however I quickly learned that is not the case. Excel simply overrides the initial clicked on series line and executes the command only for the series chosen under "Based on:" when Adding a new Trendline. I suppose there are no interpolating capabilities in Excel to compare two data series lines?
I was reading on multiple regression but that's not exactly what I had in mind either.

A rough workaround is to create three new sets of Ba, which is an average between B and A, and then do a simple linear regression for all three remaining data sets.

Is there any way within Excel, or Matlab, to achieve such a directive?

Thank you very much in advance,
Much appreciated,
Sincerely yours,

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Share your knowledge

Related Discussions

Related Forums