Higher Dimensional Trendline

Question

My data is in multiple dimensions, as below. Rather than a trendline, I guess I need a trendplane. Is this possible in Excel or a similar tool?

Answer 1

Excel does not have 3D fitting capability. But XLSurfFit, a third party Excel addin can do it pretty easily: http://3dexcel.com/excel-3d-surface-equation-fittinng/

Answer 2

I don't know if you can do a best fit based on 3D data but you can do a best fit based on each series and then create a 3D surface based on that. Is it acceptable to have it be in a different chart? Smashing a surface on top of an already busy 3D chart would probably be bad UI anyway. First, here's what my final sheet looks like:

I used a 3rd degree polynomial fit such that the surface would be of the form y = ax^3 + bx^2 + cx + d. Based on your data, you may want more or less but you can adapt this proof-of-concept to fit your need. Here's the formulas for the cells L2:L6:

=INDEX(LINEST(B$2:B$9,$A$2:$A$9^{1,2,3},1,0),1)
=INDEX(LINEST(B$2:B$9,$A$2:$A$9^{1,2,3},1,0),2)
=INDEX(LINEST(B$2:B$9,$A$2:$A$9^{1,2,3},1,0),3)
=INDEX(LINEST(B$2:B$9,$A$2:$A$9^{1,2,3},1,0),4)
=INDEX(LINEST(B$2:B$9,$A$2:$A$9^{1,2,3},1,1),3,1)

If you wanted a second degree polynomial, the array would be {1,2} and the row_num parameter for the INDEX function would only go up to 3. These formulas need to be dragged out to the right so that you get a fit for each series. Finally, you can create a second table that calculates this best fit for each point. The formula for cell V2 is:

=L$2*$U2^3+L$3*$U2^2+L$4*$U2+L$5

... and that gets dragged down and over. Create a 3D surface chart based on that data and you're done.

Answer 3

Source Change the display of a 3-D chart

Create a 3-D chart

• On the worksheet, select the cells that contain the data that you want to use for the chart.

• On the Insert tab, in the Charts group, do one of the following:

  • To create a 3-D column chart, click Column, and then under 3-D Column, click 3-D Column.

  • To create a 3-D cylinder chart, click Column, and then under Cylinder, click 3-D Cylinder.

  • To create a 3-D cone chart, click Column, and then under Cone, click 3-D Cone.

  • To create a 3-D pyramid chart, click Column, and then under Pyramid, click 3-D Pyramid.

  • To create a 3-D line chart, click Line, and then under 3-D Line, click 3-D Line.

  • To create a 3-D area chart, click Area, and then under 3-D Area, click 3-D Area.

  • To create a 3-D surface chart, click Other Charts, and then under Surface, click 3-D Surface or Wireframe 3-D Surface.

Example:

See also XYZ 3D-chart in Excel, 3D-histogram in Excel , 3 axis chart and excel 3d bar chart for more information.