I have a worksheet of (x,yn) numerical data where x are in data steps of anywhere from 10-30. Yn are arbitrary data ranging anywhere from 0.00 to 100.00. I currently have a program where I import the data and resample x using an weighted average interpolation method to yield data steps of 1 with respective interpolated y data. It is an easy process, but I would like to implement a macro for when I don't have access to the program. Below is a sample of the data set (I have also attached a sample data set below):
A B C D E
Dept Qt Ca Ill Sme
4030 64.00 1.60 29.20 5.20
4070 64.87 3.00 27.67 4.47
4100 60.50 2.33 32.43 4.80
I would like a VBA macro function that would take the upper and lower bounds of each cell in column A (4030, 4070) to produce a new data set with interpolated values for columns B-E with a column A data step of 1 (4030, 4031, 4032,...). It would even be better if the function could be a cubic spline interpolation instead of a linear interpolation. Excel can graph the above data as a the cubic spline function using a point data graph, however I have found nothing in Excel that can interpolate and export the data out of Excel to look like what the visual graph displays.
My experience with Excel/VBA is fairly limited but this will be a learning experience.
Any help would appreciated.