Complicated chart...

[johnnybravo]

Hi,

If I have a grid of numbers on the chart, is there a way to create contour lines corresponding to a certain value?

[MrShorty]

Excel is pretty flexible and pretty robust software. To answer your direct question, "is there a way to...?" Yes, I'm almost certain there is a way to do it. I'm afraid, though, that we'll need more information in order to help figure it out. What kind of data? How is the data laid out? and so on.

[johnnybravo]

I ll try to explain better:

The data I have is related to storm water storage, outflow rate and number of spills to the river. Its pretty much a matrix 10x24 so I can easily rearrange the data as it needs to be. Basically, I want Y axis to be water storage, X axis is outflow and for every combination of water storage and outflow rate there is a certain value of spill number. This will form a homogenious grid of spill numbers. What I want is to have a contour map of number of spills. It is the same logic as geographic contour maps only with different variables.

Right now I have arranged the data this way

First row = Outflow rate (X-axis)
First column = Storage (Y-axis)
Numbers inside the rectangle created by X and Y = Number of Spills (Z axis I guess)

I know there is a surface chart in Excel, I am just not sure of how to use it... Any help is greatly appreciated.

[MrShorty]

The 3D surface charts (at least through 2002) are only category based. They can't really be used as 3D XYZ scatter plots. If you can arrange your data so they will work as categories, then you might make them work. Even then, trying to represent 3 dimensions on a 2D space (monitor, paper) doesn't often generate a really useful graph.

As you have the data laid out, I see two possible contour plots that could readily be created.

1) If you plot the columns on an XY scatter plot, you'll get several series that represent contours. These contours will represent Z vs. Y at constant X. The X values will be the series labels, The x-axis will represent the Y values, and the y-axis will represent the Z values.

2) If you plot the rows, you'll get contours that represent Z vs. X at constant Y. Here, the Y values will be the series labels, the x-axis will represent the X values, and the y-axis will again represent the Z values.

You didn't say so explicitly, but I expect what you really want is a plot of Y vs. X at constant Z. To do this, you'll need to get the Z values in the left column of the table, and then map the Y values inside the table. It will then be easy enough to plot Y vs X at constant Z by plotting the rows. Excel doesn't have a built in utility for doing this. You've indicated that it is easy enough to rearrange the data. If you can rearrange the data this way, you'll have no trouble creating the desired contour plot.

One last note, if you are using lines so that you have "closed" contours (contours that make a complete "loop"), you'll need to copy the 1st point plotted in the series so it is plotted again at the end of the series to complete the loop. If all of your contours are open, then this isn't an issue.

HTH

[Power Excel]

To create a real contour plot in Excel, try Contour Plot or EZplot for Excel at www.OfficeExpander.com

There is a demo version for you to try.

Cheers!

[Power Excel]

To create a real contour plot in Excel, try Contour Plot or EZplot for Excel at www.OfficeExpander.com

There is a demo version for you to try.

Cheers!