Spreadsheet smart automated copying from one sheet to another.

The Original Data Sheet in the attached spreadsheet shows x, y coordinates and the corresponding true z values.
The Interpolation Sheet shows a different set of x, y coordinates (some overlapping), and the interpolated values imported from two different algorithms - bilinear and bicubic interpolation. There is also a column called z_truth.

My goal is to determine whether bilinear or bicubic interpolation is better.
It may be that for this initial data set that one sometimes does better than the other and vice versa.

To get a sense of how the interpolated data matched against the original data, I started copying rows from the Original Data Sheet to the Interpolation Sheet placing x, y coordinates from one near the other. If the coordinates match exactly, then there is only one copy. Otherwise, there are two copies where the original x, y coordinate falls between two x,y coordinates in interpolated sheet.

Currently, I am just going one row at a time from the Original Data Sheet and copying it to one or two locations in the Interpolation Sheet. This is taking too much time. I was wondering whether this copying could be automated.

If you could also help me with some metric to identify whether bilinear or bicubic interpolation is closer to the true values, I would appreciate that.

(I am now aware that my choice of original true value coordinates may not be so great; but this is a (good?) first start.

LVL 33
Who is Participating?
Ejgil HedegaardConnect With a Mentor Commented:
The code below is in Module1 in attached workbook.

The principle for the transfer is to find original x and y values where the absolute distance to interpolation x and y values, is less or equal to 0.02.
The result is in columns K to O
The values you have found are moved to columns V to Z, so you can compare.

The limit 0.02 is set at the line "Const Limit As Single = 0.02".
That match what you have found.
You can change the value, and run again, it takes only a second.

The differences to z_truth are quite large for those where x an y don't match , so a compare to bilinear and bicubic might be meaningless.
If z_truth is the correct value (where does that come from), the interpolations can be compared to that, by dividing with z_truth to see the relative match for each.  Columns Q and R.
In row 1 is the average.
I would say the best is the one nearest to 1 (bicubic).
The same is done in columns S and T for the transferred values, but only where x and y does not match original x and y.

Try changing limit to 0.01, to only use values where x and y are closer to each other.
Then bilinear is best.

Option Explicit

Sub CompareInterpolation()
    Dim wsOriginal As Worksheet, wsInterpol As Worksheet
    Dim rwOriginal As Long, rwOriginalMax As Long
    Dim rwInterpol As Long, rwInterpolMax As Long
    Dim arOriginal() As Variant, arInterpolData() As Variant, arInterpolResult() As Variant
    Const Limit As Single = 0.02
    Set wsOriginal = Worksheets("Original Data")
    Set wsInterpol = Worksheets("Intepolation")
    rwOriginalMax = wsOriginal.Range("A1").CurrentRegion.Rows.Count
    rwInterpolMax = wsInterpol.Range("A1").CurrentRegion.Rows.Count
    arOriginal = wsOriginal.Range("A1:E" & rwOriginalMax)
    arInterpolData = wsInterpol.Range("A1:B" & rwInterpolMax)
    wsInterpol.Range("K2:O" & rwInterpolMax).ClearContents
    arInterpolResult = wsInterpol.Range("K1:O" & rwInterpolMax)
    For rwInterpol = 2 To rwInterpolMax
        For rwOriginal = 2 To rwOriginalMax
            If Abs(Round(arInterpolData(rwInterpol, 1), 3) - arOriginal(rwOriginal, 1)) <= Limit _
             And Abs(Round(arInterpolData(rwInterpol, 2), 3) - arOriginal(rwOriginal, 2)) <= Limit Then
                arInterpolResult(rwInterpol, 1) = arOriginal(rwOriginal, 1)
                arInterpolResult(rwInterpol, 2) = arOriginal(rwOriginal, 2)
                arInterpolResult(rwInterpol, 3) = arOriginal(rwOriginal, 3)
                arInterpolResult(rwInterpol, 5) = arOriginal(rwOriginal, 5)
                Exit For
            End If
        Next rwOriginal
    Next rwInterpol
    wsInterpol.Range("K1:O" & rwInterpolMax) = arInterpolResult
End Sub

Open in new window

phoffricAuthor Commented:
@Ejgil Hedegaard,
Thanks for the reply. I got back very late, so I'll take a look at this tomorrow. Thanks again.
phoffricAuthor Commented:
>> If z_truth is the correct value (where does that come from),

Here is the function I used to generate the true values:
      Z = 1 / ((2*(X-.4)+ .5*(Y-.2) -.3)^2 + .01) + 1/ ((X/3 + 3*Y -.9)^2 + .04) - 6;
It is a variation of humps which is a 1d function.
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

phoffricAuthor Commented:
I was working at home today and my laptop finally died from the beating out took  when the new  Windows 10 update came by last week and did  havoc to my visual  studio 2015 community edition when trying to build the gnu scientific library, which is what I used for the interpolation. Literally it took two days of constant disk activity to repair and  install  components due to the update.

But I could see your spreadsheet on my kindle tablet. You did an excellent job. Thank you so much. I don't know the language so I'll ask a different question to get an explanation.  I couldn't change the limit to 0.01 on the tablet or on my laptop using libre office calc. But tomorrow I'll be at the office and I will use excel.

I was thinking of adding a standard deviation into the mix as well as the mean that you have already done.

I would appreciate instructions on how to access your code in excel and how to run it. Thanks again for your help.
phoffricAuthor Commented:
By rights I should have included some 3d pictures of the surface. It has multiple local maximums with different curvatures. By rights I think I should be careful to select original coordinates to be close to those max locations.

Also most of the region varies very smoothly. Those regions should be weighted less than where the multiple humps are located. I'll try to do that after I get another computer and get everything installed again. I may be able to find a workstation at work, but at the moment the ones I need are being used. I purposely chose the function to have some hard to interpolate regions as well as relatively large smooth regions.
Ejgil HedegaardConnect With a Mentor Commented:
To edit the VBA code press Alt+F11, and in the project explorer at the left, double click on the module if it does not open with the module active.

To run it, press F5, or the button that looks like a tape recorder Play button.
Or go to the developer tab, select macros, and run from there.
Or a button could be place on the sheet.

Standard deviation will also tell something about how good the match is.
But the differences are large, so in my opinion none of the interpolations match.
phoffricAuthor Commented:
Many thanks for your program and your analysis. I will be able to use this with better choice of sampled coordinates and do a better comparison. Thanks again for saving me many hours of tedious, error prone copying.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.