VBA Newbie looking for linear or polynomial fit to missing dats

[happy_camper]

Hello Good people of the world:

I promise I have looked at a bunch of linear interpolation resources here and on the web; however, since I'm just learning loops and basic VBA and Python, I'm convinced that I need more time to even be able to assimilate resources out there and make it into a workable solution. I've been struggling with this for a week, and I ran across this website during my research

I have just 3 columns.
First column is just a count of the days (1,2,3....)
Second column is a continuous stream (has a value for each day).
Third column has several missing values. (with values on just a few days)

I'm trying to interpolate in the 3rd column so that all missing values are filled out. After that, I'm trying to make a cumulative plot of both the streams and compare them(which I will be doing manually later). But to make my cumulative curves, I need the entire 3rd column filled out by linear interpolation or a spline/polynomial interpolation.

I would greatly appreciate your help here. I will give back to the community once I get better at coding some day. Thank you!

[Paul_Hossler]

Easiest way is straight line over the gaps

26581

2 gaps between days 4 and 7 so 1/3 of day7 minus Day4 and 2/3 of day7 minus Day4.

The 'checks' are for 0/3 and 3/3

What about the red points? X = 0 but Y non-zero. In this POC I just treated them as missing data

[happy_camper]

Thank you very much Paul. I really appreciate it.

However, I have several hundreds of lines with several windows of irregular open gaps in column D. In this example, you have frozen the ordinate(Y axis) and hard coded the 1/3 to calculate slopes for that particular gap, correct? But, how could I recalculate the slope for the "new gaps" as I go down the list and use the interpolation formula? Or am I missing something? And for missing Y values in column C, I will just use a formula at the end and make column D and Column C zeros.

Thank you Sir!

Virtue, liberty, and independence
:D

[happy_camper]

Also, Paul,
I ask this because I see that you have used the Point-slope form of the linear equation. My trouble is gettign the slope for a continuous stream at different gaps? In this case you have provided an absolute reference, and I was wondering how I could get that to change as I go down the column for the new gaps. Thank you!

[Paul_Hossler]

In this POC I just treated them as missing data

This was just a proof of concept for the interpolation algorithm

A macro which I'll work on will do the data set

Give me a little time to put something together

[p45cal]

Try this 3-line snippet while your Sheet1 is the active sheet having removed any filtering first:

For Each are In Range("D2:D15049").SpecialCells(xlCellTypeBlanks).Areas
are.Offset(-1).Resize(are.Rows.Count + 2).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Trend:=True
Next are

You could also test changing Type:=xlLinear to Type:=xlGrowth
Is one of them giving you the results you're looking for?

[happy_camper]

Yes Sir. Copy that. I really appreciate it :)

[happy_camper]

Hello p45cal:
Thank you for helping too. I put your code within a subroutine and tried to run it, but I was getting the following error:
2658626587

[p45cal]

Those attachments (pictures?) are showing up as invalid so I can't see what they are.

[p45cal]

Try this 3-line snippet while your Sheet1 is the active sheet having removed any filtering first:Have you removed filtering first?

[happy_camper]

I tried to attach images of what I did. But, I went ahead and uploaded the images on google drive. Kindly access those at the link below. Thank you! Appreciate you rhelp.

https://drive.google.com/open?id=1xE9c3s3BqJtbCPfxmdjQ2ONFbZs7U7KX

[happy_camper]

Have you removed filtering first?
And yes, I did remove the filters. I had the data as a table and I converted the table to Range before I ran those 3 lines of code in a subroutine.

[p45cal]

This is what I get when I run the macro on your file after clearing the filter and converting the range to a table (and back again afterwards, it didn't make any difference):
26589

Attached is your file with: macro added, button to run that macro, filter removed. Just click the button (near cell G22) and it should work.

[happy_camper]

It works. I really appreciate your help.

If it is not too much trouble for you, would you kindly let me know what those lines mean?

1st line of code:
You are using "are" as a counting variable and looking for each value in column D. Does that code start from the top and catch each blank value? What does "areas" do?)

2nd line of code:
You are offsetting row by 1 to go upwards to the non-black value? Then, I didn't get the count +2.

3rd line of code: You iterate through "are variable" until the end of your range D2:D15049 is reached. This is how I decipher it.

Thank you!

[happy_camper]

Paul: Thank you, also for your initial replies.

p45cal's reply has helped me out. Thank you!

[p45cal]

Working on your sheet, before the blanks are filled in, if you select cells D2:D15049 (it will save you a lot of scrolling about if you press F5 on the keyboard, and in the Reference field that pops up type in the full address D2:D15049, and click OK), then press F5 on the keyboard again, then click the Special… button, choose Blanks, and click OK. You've just done the vba equivalent of:
Range("D2:D15049").SpecialCells(xlCellTypeBlanks).Select
If you examine what's been selected you'll see that it's made up of multiple (non-contiguous) areas, each being a single cell or multiple cells, this time, contiguous cells. The code iterates through those areas. are is a name of my choosing, which takes on an area at a time, because instead of using .Select at the end of the statement I used .Areas. So are is an object variable being a range of cells

You'll get the idea if you step through each of the following three macros by pressing F8 repeatedly on the keyboard (while keeping an eye on whats happening on the sheet):
Sub macro1()
For Each are In Range("B2:G17").Rows
are.Select
Next are
End Sub

Sub macro2()
For Each are In Range("B2:G17").Columns
are.Select
Next are
End Sub

Sub macro3()
For Each are In Range("B2:G17").Cells
are.Select
Next are
End Sub


Now, using just the normal Excel user interface, select a blank range you want to fill in, but include in the selection the cell above and the cell below (which both have values in). Now got to the Home tab of the ribbon, find the Editing section and choose Fill, and from the dropdown choose Series…, then in the dialogue box that pops up, Choose Columns in the Series section, choose Linear (or Growth) in the Type section, and put a tick in the Trend checkbox, then click the OK button. You've just done the equivalent of:
are.Offset(-1).Resize(are.Rows.Count + 2).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Trend:=True

So that you can see what the .Offset(-1).Resize(are.Rows.Count + 2) is about, step through the code below with F8 watching the sheet as it does so:
Sub LinInt2()
For Each are In Range("D2:D15049").SpecialCells(xlCellTypeBlanks).Areas
are.Select
are.Offset(-1).Select
are.Offset(-1).Resize(are.Rows.Count + 2).Select
are.Offset(-1).Resize(are.Rows.Count + 2).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Trend:=True
Next are
End Sub

are.rows.count is a count of the number of rows in are, I add 2 because the area of cells we need to process with .DataSeries needs to include 2 more rows than the size of the blank area we're trying to fill, one cell above and one cell below.

[paulked]

Fantastic explanation, thank you.

[Paul_Hossler]

@p45cal -- good approach. I've never used DataSeries so it was an interesting read, and certainly easier than the 'brute force' approach I was thinking about

[p45cal]

I know exactly what the next 'aah, but what if…' question will be from happy_camper…

[happy_camper]

hahaha. Well, I'm going to process this first p45cal! And then the aha moments or aahhh.. what if's may pop-up. But, I will not waste your time anymore. This is already a great help. I was just trying to learn during the process and not simply find a tailor-made solution you guys help me out with. Trying to take this opportunity to learn. That's all :D Thank you all for the support. I appreciate it greatly. :)