interpolate continuous data in excel 2013

Question

i have a set of daily data. the problem is that there are a lot of missing data.

for an example there is a data on 7th oct, but the next 5 days are missing.

there is also missing data at the end of the month where the last 5 days are missing.

how should i interpolate the data?? can anyone help me??

Answer 1

With the logic I mean how you want to interpolate the data. Do you want to calculate an average of all previous numbers? Or maybe calculate an average of the last 30 days?  The last 15 days? This is a decision I cannot make for you, because I do not know what your business needs to see.

In the following screenshot, see the data interpolated for the outage days. The formula in column D is

=IF(OR(B2=0,ISBLANK(B2)),AVERAGE(D1:$D$2),B2)

It averages all previous values, including previous interpolations.

The formula in column E uses only the last 15 days and calculates an average of that

=IF(OR(B2=0,ISBLANK(B2)),AVERAGE(OFFSET(B2,(MIN(ROW()-1,15))*-1,0,MIN(ROW(),15),1)),B2)

See the difference in results in row 22, where the average of the last 15 days gives a different result than the average of all previous days.

I'm not a very good mathematician, so there may be better formulas to get you the interpolation you desire, especially when it comes to regression. Please read on below the screenshot.

The next bit is about arranging the data.  You will not get an easy result in a format like your second screenshot. That screenshot shows a report layout and it is very difficult to apply formulas to a report layout.

Instead, may I suggest that you collect all your data in a "Data" sheet. You can then use the interpolation formula in that data sheet (as in the screenshot above).

Then, build your report on a different sheet, let's call it "Report". It can look like this:

Column A has the numbers 1 to 31. Row 2 has dates like 1-Jan-2013, 1-Feb-2013, 1-Mar-2013, etc, formatted to show only the month name.

The formula in cell B3 is

=INDEX(Data!$D:$D,MATCH(DATE(YEAR(Report!B$2),MONTH(Report!B$2),Report!$A3),Data!$A:$A))

Copy across and down.

Then use conditional formatting to highlight the weekend days. Select the cells $B$3:$E$33 and apply a conditional format with the formula.

=LEN(B3)=0

Make it green.

That's it.

Add more data to the data sheet. When a new month rolls along, add the new date (1-May-2013) into cell F2 of the report sheet. Copy the cell from E2 to F2, then fill F2 down. Now the month of May is populated with the data from the data sheet.

You can see a sample file here in my SkyDrive

Please let me know if that works for you.

Answer 2

it may help in the first month. but it can't help if the missing data occurs in the early month.

for an example the above data. can you help me??

Answer 3

What kind of interpolation do you have in mind? Average previous values? If so, you can build a helper column with a formula like this starting in D2 and copied down

=IF(OR(B2=0,ISBLANK(B2)),AVERAGE($B$2:B2),B2)

This assumes that the screenshot data is in columns A to C with labels in row 1

Answer 4

*some example of the data

Answer 5

Hello,

in order to answer your question, please post a sample data set and mock up the data that you would want to see as a result of a formula or interpolation.

It is not possible to answer your question with the information you have given in the question.