Excel Chart

Topic

New Reply

Hi,

I’m trying to create an Excel (ver. 2002 SP3) chart that will change the dimensions of the geometry based on X, Y and Z values that are entered in cells B1:B3. The geometry is of two rectangles at a 90° angle to each other and have a common edge. So if I change any of the (XYZ) values, the chart would be updated showing the new rectangle dimensions. I’ve attached an example spreadsheet to illustrate what I’m trying to do.

I know that I’ve seen this done before somewhere on the net but I don’t remember where it was or how to do this. Can someone guide me as to what to do or to a similar example?

Thanks.

Reply | Quote

Replies

It probably could be done with a macro to draw the 2 shapes given a perspective, but I chose to do it with formulas.

A wireframe would be relatively simple to setup in an XY chart, but would not fill in the planes.

I used an area chart with a blank range, an XY plane and and XZ plane range. I overlayed an XY wireframe to keep the 2 axes “square” (it draws the outlines and sets the projected axes ranges) otherwise the automatic scaling would adjust and have different ranges in the projected x and projected Y axis.

I used 30degree projection for both axes but these can be modified if desired. The area chart X-Axes is a timescale to allow actual values and not categories, but this requires integers so the actual ranges are multiplied by 10,000 to eliminate the rounding errors. In my testing it seems to work reasonably well.

Steve

Reply | Quote

It probably could be done with a macro to draw the 2 shapes given a perspective, but I chose to do it with formulas.

A wireframe would be relatively simple to setup in an XY chart, but would not fill in the planes.

I used an area chart with a blank range, an XY plane and and XZ plane range. I overlayed an XY wireframe to keep the 2 axes “square” (it draws the outlines and sets the projected axes ranges) otherwise the automatic scaling would adjust and have different ranges in the projected x and projected Y axis.

I used 30degree projection for both axes but these can be modified if desired. The area chart X-Axes is a timescale to allow actual values and not categories, but this requires integers so the actual ranges are multiplied by 10,000 to eliminate the rounding errors. In my testing it seems to work reasonably well.

Steve

Thanks so much, Steve. It helps me a lot.

How does the timescale work in an area chart?

Reply | Quote

I had a chance to play some more. I added lines to indicate the length of each “axis”…

Steve

Reply | Quote

I am not sure exactly what you are after.

The area chart (like most of the charts in XL) uses a “Category” for the X-axis which limits the versatility and requires a strict order.

[The XY is one of the few which actually uses a “Value” and treated as a number and can go in any order. Just like the Value-Ys can go up and down on most charts, On an XY chart the order the points are entered are the order in which they are put into the chart. This allows the direct drawing of something like a rectangle which has a point then a point to the right and then up and then left and then down. This going to the “left” is not possible with a category axis]

The “Category X” may either be a true “category” which is essentially text with each x value listed equally spaced from each other and in the order listed or a “time-scale”.

The time-scale is numeric, But has “problems” in doing what is needed. It is only integer, (partial “days” are not allowed) which can lead to rounding errors if you want decimal values (which are needed for this type of drawing). I minimized the rounding error by multiplying the values by a large number.

The other problem is that even though they are integer values, unlike an XY chart axis, the order the values are plotted is the NUMERIC order not the listed order. There is no possibility of going from right to left, which requires, for this purpose to use logic to put the points being plotted in numeric order. In this problem at least there were only 2 possibilities. XY1 is always the leftmost, XY3 is always the right most, but the order of XY2 and XY4 will be dependent on the relative length of X vs Y and the angle of the 2 projections.

The other challenge with the stacked area chart is the filling. This required a range for the blank area under the XY plane, a color for the XY plane and range for the XZ plane. part of this stacking requires not only knowing the points of the rectangles but also the points on the sides of the rectantangle on the projected X axis and Y axes.

I hope this answers your questions. If not could you be more specific on what you would like to know…

Steve

Reply | Quote

Thanks for the detailed explanation. Now it’s more clear what you were doing with the stacked area chart.

One other question. In your original response you mentioned an option of using a macro to draw the two shapes in order tocreate the same effect. Do you think this is simpler to put together than the area chart? The reason that I’m asking this is because I am planning to create a spreadsheet with a few other shapes, such as a circle and a cylinder. The main objective of this spreadsheet is to be able to calculate the shape factors (or view factors) between various but basic geometrical shapes. The “dynamically” changing figures are there to help the anyone understand how the shapes vary relative to each other.

Regards.

Reply | Quote

Here is a file with some code.

The code essentially looks for a shape called “Window” and gets the size of it. it then deletes the previous shapes. Bsed on the XYZ points you give it as parameters it calculates the projected coords of the 6 points. it then resizes the figure based on the size of the window and the defined min border.

After calculating the size factor it adjusts the points and then creates the 2 objects and colors them.

There is also a worksheetchange procedure which looks for changes in B1:B3 and if any are made, it redraws the diagram. SO change B1, B2 and/or B3 and the figure is redrawn.

The Proj X and Y angles are set to 30degrees (to match your lines) but can be defined near the start of the code (along with the color and the minimum borders).

For circular objects I would use smooth line with a polygon of maybe 10 points (if too jagged, increase the number of points). You will have the radius and just loop through the angles 0 to 2*PI (VB and XL use radians) and calculate the X,Y with Cos/Sin (essentially polar coords) to get the points and then project each into the Px / Py space depending on how it is set up.

Steve

Reply | Quote

Great! This is what I was looking for.

Steve, I was wondering if you could also help me figure out the equation for a perspective view for a circular object. In the attached worksheet I divided the circle into 10 polygons and offset it, such that its center lies at one radius away from the zero. However, I don’t know how to proceed now that I want to project the coordinates into a perspective view. I was searching throughout the internet for such a transformation but I didn’t come up with anything. Can you let me know what equations I should use?

Thanks.

Reply | Quote

Here is a 3d Projector calculator with momre general transformation (given an XYZ point along with the 2 angles (rotation of XY plane and tilt of Z-axis) it calculates the proj XY points. Using those calcs it draws a circle (and Cener point) whose radius and center point can be changed. It also plots the XYZ axes for reference.

I don’t have a reference for the calcs. I recall finding the transformation in a QBasic book years ago and transformed the calcs to a spreadsheet (it was Lotus123 at the time). I dusted off the spreadsheet, but do not believe I have the QBasic book any longer…

Steve

Reply | Quote

Thanks Steve. I appreciate all your help.

Reply | Quote