Replies

Thanks for your help.

Excellent, this worked. Last question, how did you get the nice chart colours (for the columns and the legend)? The colours that I get are the standard fill colours and they are pretty plain.

Thanks Rory, that’s what I wanted. I tried to recreate what you’ve done (so that I can do it myself in the future), but I was not able to get the same chart that you got. The attached file is what I got. Can you explain to me what you did different than me?

Thanks for all the help.

When I modified the table for a better explanation, I forgot to erase the “Desired Output” table. I appologize for the confusion.

What I’m trying to do is to create an ‘expanded’ table based on the table in B5:D10.

The updated spreadsheet is attached.

The intention of the data is to interpolate between fixed numbers. I modified the attached spreadsheet to show what I intend to do. I also found a way to add the time to the new table based on the number of increments. For example, the equation in cell B17 is:

=IF(A20$D$5, A20($D$5+$D$6), A20($D$5+$D$6+$D$7), A20($D$5+$D$6+$D$7+$D$8), A20<=($D$5+$D$6+$D$7+$D$8+$D$9)), B19+($B$10-$B$9)/$D$9, "ERROR")))))

As you can see it's a long equation. I was wondering if there is a better way of doing the same thing.

Thanks.

Works great. Thanks.

Great, this is what I wanted. I can’t believe the equation is this simple. I’m so glad that this forum exists. Thanks all!

Just for interest sake, I was sure that the same could have been achieved using the ‘INDEX’ and ‘MATCH’ functions to get the “to” cell address so that the summation equation would have been ‘=sum(A6:A#)’, where”#” corresponds to a row number of a value not greater than the value in C1. Anyone knows the full function for this?

Thanks Steve.

Thanks Steve. I appreciate all your help.

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.

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.

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?

Oops, sorry. Here it is.

Much appreciated.

See the attached sample workbook; it uses intermediate formulas to calculate the difference.

BTW it would be easier if columns A and B contained regular date+time values.

PS You can’t insert tab characters in a post. You can use the [b]code[/b] … [b]/code[/b] tags to create fixed-width text in a post:

Code:

Starting	  Ending
000/00:35:00  001/19:45:00
000/00:35:00  008/00:45:00

Thanks Hans, but where is the attached workbook?