Interpolate data

[Guest]

How can I interpolate data between two points if the gap between them varies?

I have payment rate data for a number of transactions that is reported
either monthly, quarterly or semi-annually depending on the transaction. The
data is exported from an external database and is reported monthly for all
records and pasted across the row with Column A as the record name, Column B
the reporting frequency (Monthly,Quarterly,Semi-annual) then columns C:# are
periods 1-x. As the report from the database provides monthly data for all
records those that report quarterly have a value for period 1 then 2 blank
periods and a value for period 4. The semi-annual ones have 5 blank periods
in between data.

I need a formula that will interpolate the data in the gaps, however each
time I export the data the number of records will vary so row 2 may be
monthly one time and quarterly the next time.

Any help and advice please!

 

[Guest]

Checkout the FORECAST() function in Excel Help. Not only does it forecast,
It can easily interpolate between two points: (x1,y1) (x2,y2)

 

[Lori]

One way:
1. Choose tools-options-calculation-iteration e.g. max change:
0.000000001.
2. Select all blank cells in the range F5-Special-blanks
3. Average the two adjacent cells, so if D2 is active enter

=(C2+E2)/2

and execute with ctrl+enter. Paste-special values to remove formulas.