Creating an average line on a chart that takes into account Interpolated values.

[xandermacleod]

Hi there,

I have a line chart where I want to create an average line that takes into account not just the data I have put into the cells, but also interpolated values 'between' those cells.
I cannot figure out a way of doing this.

Please see the attached document for further info about what I mean.

Hopefully I can keep a 'staggered' date system; but all suggestions are welcome.

Regards

Xander

[Andy Pope]

You need to calculate the interpolated values and take date distances between know values into account rather than the number of empty cells as you describe in the workbook.

[xandermacleod]

this is certainly a great start, but the main problem I see with it is that I need to insert each formula 'after' the known values have been put in.

The example workbook is literally just an example to illustrate the issue at hand. My final workbook has quite a few hundred cells that need filling.

I would be happy to have 2 (or even 3 graphs). 1 for known values, one for interpolated values, and perhaps a final that combines them. But ideally I need some kind of formula that automatically works out which cells hold actual values, and then provide interpolated data. In your attached workbook the cells with known values are hardcoded in.

Is there a way to identify them automatically, wherever they may appear?

(regardless. Thanks. This is a great starting point, if nothing else)

[Andy Pope]

I'm not aware of an easy, or difficult, approach to interpolating the empty cells simply with formula that can determine the known data points.
Somebody else maybe, you could try posting another thread for that purpose (reference this thread if you think it would help clarify).