Use X-Y-Z data to create a surface plot

Hello all,

I have X-Y-Z data where the Xs and Ys are not consistent - there are within specific ranges, but they are inconsistent.

I will choose X and Y values to create a table that can be used to create a surface plot - is there a formula that can do the interpolation based on my actual data and chosen interval values?

Thanks,
Bernie

The attached is a much simplified set up of my problem.

You could create a 2 variable data table.

https://support.office.com/en-gb/art...2-77542a5ea50b

Unfortuantely, I don't have an equation for my data - I only have the data.

Ah, your example simplified the problem too much.

I am scratching my head trying to see the (math) relationships between the data and output table expected results.

I am not seeing interpolated values in the interval ranges ... maybe I am not interpreting what the intended interval ranges are.

What am I missing?

Stated another way, I need the best estimate for the X Y pairs in the table, based on the data. So, X = 1 Y = 1 does not exist in the data table, but I want to estimate Z (which would be 2, based on the relationship of my example) without having the X Y pair of 1,1 anywhere in my data. There is no requirement that any of the the pairs that I am interested in (the nine pairs formed by 1,2,3 and 1,2,3) exist as such in my data. And the relationship is not necessarily linear, but I am willing to use an interpolation between points in my table as the basis of the check against my data, trying to minimize the error.

Just out of curiosity, can you share any context around what this data is or what you are hoping to show with, for example, the Z estimate? Trying to understand what other options might provide a simpler solution.

I am trying to create a surface chart to show a temperature map of a surface, based on thermocouple data. But the thermocouples were not properly spaced, nor in the correct number, to meet Excel's limitation of a rectangular data table with set intervals. I came in late to the project, after all the data was collected, so re-instrumenting the surface and re-running the tests is not an option.

I can - and may have to - simply move the data onto a grid (by changing the X Y values to set values) to show the graph - but that would distort the resulting representation of the actual values, so I was hoping that there was some two-way reverse interpolation technique to populate the table without distorting the resulting map by very much.

I'm exploring an iterative approach where I calculate the average for every point within sections of a new grid, then set the surface (the four points defining it) to that average, then going to another section, getting an average, adjusting the common points, etc.. then checking the calculated value for every point based on double linear interpolations, until I minimize the error measurement.

The other approach I am contemplating is to use a 'finer mesh' of points so that every data point is on or very close to an intersection, then interpolating every which way to fill in the missing points.

I'm afraid that's over my head.

Does this come close to what you are looking for?

In column K this array formula.

Formula:

Please Login or Register  to view this content.

In row 6 this array formula.

Formula:

Please Login or Register  to view this content.

Then in L7:N9

Formula:

Please Login or Register  to view this content.

All I am able to do is duplicate expected results.

	G	H	I	J	K	L	M	N
6		1	2	3		0.75	2.00	3.00
7	1	2	3	4	1.00	2	3	4
8	2	3	4	5	1.75	3	4	5
9	3	4	5	6	3.00	4	5	6