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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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 HedegaardCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.