Surface Chart with a Line

[lore]

Hi,
I am trying to combine a 3d surface chart with a line. To make a comparison, if the surface chart looks like a mountain, I would like to add a trail on it.

Here an example of the surface chart with its data table:
https://dl.dropboxusercontent.com/u/3018452/surface_chart.xlsx

The left table contains some theoretical values that are used to plot the surface chart.

On the right there are the "observed" data that I would like to combine with the existing surface chart, possibly in the form of a line or vertical columns.

In the attached sheet the observed values are the same as the theoretical (of the left table), so they will float exactly on the surface, however in practice we will observe values that are above or below the theoretical surface and in between of the Y scale (430, 437,440)

I thought to overlap an other surface chart with the observed values chart, however I can not make the theoretical values chart transparent , also I think that overlapping 2 surface charts is not really reliable.

Any inspiration to move a step ahead would be great,
thanks

 

[Faseeh]

Dear Lore,

As far as i know, we can not have a secondary axis in a surface chart so we can't create combination chart here so try plotting it on two dimensions.

 

[lore]

Thanks Faseeh,
I am aware of what you are saying; my hope is that there might be some alternative & creative approach to the problem...that did not come to my mind yet.

 

[Faseeh]

Hi Lore,

There is one chance that i can think about and that is to replace the values in the first table with that in second so that when the value in the second table is less then respective value in the first, you should get that line displayed int he chart. Let me try it.

 

[lore]

Hi Faseeh,
thanks for sharing the idea, however this wont't work either for 2 reason:
-Theoretically (like in the xls included) the value of the line are the same as the ones of the surface. Just imagine to draw a trail on a hill.
-Even if we'll artificially rise the values of the line to make it stand out, the final effect will be a surface with on top a "triangular hill". The value of the line will be joined to 2 other existing values on the surface.
-Altering the values on the line is in any case misleading because one interesting piece of information that I would like to visualize with this chart would be to see when this line dips in and out of the surface and of how much.

 

[Faseeh]

Hi lore,

Thanks for clarification, but i am not going to give up that easily. can we create a two dimensional chart (a scatter chart that resembles the original surface because in that chart we can show the streak of line you required. But transforming this 3d data to 2d is real challenge. What do you say?

 

[lore]

Well, I understand that color coding could partially substitute the 3rd axis for what regards the capacity to read the numbers in it, but still...the 3d gives an extra useful touch. Let's keep 3d.

 

[lore]

OK...looks like I got lost in the dark side of Excel and ended up here, the site was linked on Chandoo.org so I think it is OK to include the link. http://excelunusual.com/basic-3d-2d-perspective-visualization/
Basically, instead using a 3D excel chart, a 3D chart including capacity to tilt and rotate is created by calculating Cartesian points.
What amazes me is how smooth the file runs, the 3d series I included in my first post is already very heavy on my Excel and slows down the system.

This is really Excel porn! I will post some updates once I will have some time to look more into it, meanwhile I am open to ideas and suggestions.

 

[Faseeh]

Hi lore,

I think i already shared this idea with you, but have been unable to materialize it due to my pre-occupation

...can we create a two dimensional chart (a scatter chart that resembles the original surface ............ But transforming this 3d data to 2d is real challenge.

 

[SirJB7]

Hi, all!
Porn? Where did I read porn at an Excel website? I've done crazy things with Excel in my life, but porn?? I swear I never...
In fact where did I read it is not the main thing, the most important is... where're the download links???
Regards!