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!

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


"pjd33" wrote:

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!

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.

On 8 Mar, 11:06, pjd33 wrote:
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!