Help. Interpolating in excel

davetrousers

Hi I have a query, how do I interpolate in excel?

I have some daily data, but for some days there is no data, so how do I interpolate some values to fill the gap. This is what I have:

27-Jul-04 135.6 28-Jul-04 0 29-Jul-04 0 30-Jul-04 0 31-Jul-04 0 01-Aug-04 237.25
How can I interpolate values for 28th to 31st July?

Thanks

davetrousers

Damned formatting, these should be in columns (ie the date in column A and the value in column

davetrousers

27-Jul-04 135.6
28-Jul-04 0
29-Jul-04 0
30-Jul-04 0
31-Jul-04 0
01-Aug-04 237.25

superscaper

Well mathematically I suppose it depends on how you assume the data behaves. Making a typical assumption that it's linear then you basically want to go in equal incremental steps every day until August. So (237.25-135.6)/4 added to the value/cell before it, so for 28th it'd be 135.6+(237.25-135.6)/4. Not thinking clearly today but I think that's right (haven't checked).

superscaper

Just to turn that into "Excel speak", assuming your above table started at A1, then the formula you'd want in the first of the "zero" cells would be B1+($B$6-$B$1)/4. I used the $ because they're absolute references so you can just copy and paste that exact formula to the other cells without the references being messed up.

davetrousers

OK thanks, got it now

Stompa

If you leave the unknowns empty, you can select the range up to and including the known values either side of your unknowns, then use Edit > Fill > Series...

superscaper

Stompa wrote: »

If you leave the unknowns empty, you can select the range up to and including the known values either side of your unknowns, then use Edit > Fill > Series...

I thought there'd be an easier way in Excel. I was only thinking about the actual maths approach first. I presume that the series function assumes a linear interpolation as I did.

Stompa

superscaper wrote: »

I presume that the series function assumes a linear interpolation as I did.

By default, yes. It does also offer some other alternatives, though I've never used them and quite frankly it's not entirely obvious what they're for (and they don't appear to take notice of both the start and end values).