Deriving continuous Data from Connected XY scatter line

Hi,

I am trying to find a way to derive/interpolate the continuous line data that excel uses to connect the points on an XY scatter chart.

Essentially my scatter chart represents a weighted histogram with the X axis representing 0-100%, with each point (and by extension the line connecting them) representing the portion [of 100%] at a particular revenue level, on the Y axis (attached below).

I need to find a way to obtain this 'connected line' data in continuous format for, say, 1% intervals…this data is then to be further interrogated in excel. Does anyone know a way to obtain/interpolate this data?

Thanks in advance,

Blofeld

Hi Andy, thanks for your help...

Someone on another forum suggested using the Trend function to derive the missing data (See the sheet attached).

This was the suggested function to fill in the blanks:

=TREND(OFFSET(A$2,MATCH(D2,B$2:B$26,-1)-1,,2),OFFSET(B$2,MATCH(D2,B$2:B$26,-1)-1,,2),D2)


However, at first glance this seems to work, but when I plot the new line chart, you can see I've somehow managed to arrive at an mirrored version of the original XY plot.

My gut feeling is that this is a school boy error, Do you think I should be after the X values instead?

Rgds

Blofeld

Outstanding!

Your help is much appreciated....

Rgds

Blofeld