Interpolated charts

[Ben]

When chart data contains #N/A between 2 data points there is a feature in
Excel that interpolates the data in the middle. This is very useful but is
there a way of getting hold of that interpolated data. I mean the actual
numbers. It would save me a lot of time having to calculate them myself.
Thank you

 

[Andy Pope]

Hi,

You need to calculate that value using a formula

Assuming the NA value is in cell B4 this will tell you the mid Y value

=B3+((B5-B3)/2)

Cheers
Andy

 

[David Biddulph]

But of course if the X-axis spacing is not equal you will need something a
little more complicated to calculate the appropriate Y value.

 

[Andy Pope]

You only need to apply the same formula to the X values in order to get
the mid x point.

 

[David Biddulph]

Yes, but the OP may be talking about a situation where he has a defined X
point, not mid-way between the adjacent ones, and for which the Y value is
NA() and he's looking for an interpolated value.

As usual, the answer depends on the question.

 

[Andy Pope]

Yes, you're right.

More like this then,

CalcY =PrevY+ (NextY - PrevY)/(NextX - PrevX)*(RequiredX -PrevX)

Cheers
Andy

 

[James Silverton]

Hello, Andy!
You wrote on Mon, 16 Oct 2006 13:52:12 +0100:

AP> More like this then,

AP> CalcY =PrevY+ (NextY - PrevY)/(NextX - PrevX)*(RequiredX
AP> -PrevX)

AP> Cheers
AP> Andy

AP> David Biddulph wrote:
??>> Yes, but the OP may be talking about a situation where he
??>> has a defined X point, not mid-way between the adjacent
??>> ones, and for which the Y value is NA() and he's looking
??>> for an interpolated value.
??>>
??>> As usual, the answer depends on the question.

It's probably a good idea to let well enough alone and I would
suspect that the replies are very adequate! However, any
numerical methods book will have many pages on different types
of interpolation The Abramovitz and Stegun "Handbook of
Mathematical Functions" is an example.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

 

[Jacky]

Hi,

I'm plotting a graph based on a table of values, for a curve that is derived
from different conditional factor values calculations. This graph is then
used as reference table to know the volume of liquid in container, from the
depth of liquid input by the user. How may i formulate the interpolation so
that the user just has to input the depth of liquid and excel will
automatically tells the liquid volume in container?

Referencing formula only associate the depth value to nearest value or so,
and not interpolate to produce the more accurate result. *Please note that
the data plotting is a curve and not a straight formula. Or is excel able to
translate the graph into a curve formula? If this is possible then it be
great...

Thanks for the attention...