On an xy scatter plot, how can I combine multiple series where the x values aren't the same into a combined line? I've attached a sample spreadsheet demonstrating what I'm asking. The yellow and blue data would be the only data present. The green data/line is the desired output.
Excel charts do not analyze our data for us, so we have to do the data analysis part.
What does "combine" mean to you in this case? It looks to me like it means "add the two functions together", is that correct? Here's how I see this analysis happening:
1) A linear interpolation step to determine y2 at the points that are in between the given points, and to determine y1 at points in between given points.
2) Once the linear interpolation is done, it should be a simple addition.
The most difficult part is the linear interpolation step, are you familiar with strategies for programming linear interpolation into Excel?
Originally Posted by shg
The answer probably lies in potting named ranges where the named range contains the formula for computing the combined values. I've looked at it and I understand 0,0 and 6,6. I have no idea what How you get 1,4 from 1,3 or 2,7 from 2,2, etc.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Here's another version of the spreadsheet showing how I get to the manually combined data. The source data is so erratic that a curve could not be fit to it (e.g. trendline interpolation).
Perhaps not a "full data set" regression, but it appears to me that you are using "piecewise regression" or "linear interpolation". Am I correct? If not, you will need to explain how you are determining the intermediate points before we can program Excel to do it.The source data is so erratic that a curve could not be fit to it (e.g. trendline interpolation).
Sometimes, I think it's too bad that Quattro Pro is not easier to come by. In QuattroPro, this would be easy using the @LINTERP() function. With your X values in column E, column F would be a simple @LINTERP($B$3:$B$9,$A$3:$A$9,E3)+@LINTERP($D$3:$D$9,$C$3:$C$9,E3). [Except for F9, because most linear interpolation algorithms will not default to y1=0 at x=6). But Excel does not have a built in linear interpolation function, so you have to program the linear interpolation in.
The basic strategy I recommend for linear interpolation is illustrated in this post: http://www.excelforum.com/showthread...=1#post3904113 You build a lookup table with the "slope" and "intercept" for each "segment" of each line. Then you can use a simple lookup function to return those parameters and compute the intermediate points. You will probably need to adapt it a little to make sure you get 0 for the points outside of the defined points.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks