Drawing level contours?
Madra Rua
I need to draw 2-d level contours in Excel 2000.
For example, I have values spaced out in a matrix, like
6 7 8 8
5 6 7 7
7 6 5 6
I want to draw the lines (level contours) with fixed values, like 6.5, 7.5,
etc
This is a trivial example; my problem has a bigger matrix. The process would
use interpolation and graphing.
tia
Rua
Madra Rua
post.
Jon, I would appreciate a few pointers to the Excel plotting, even though
it may not be very accurate. Linear interpolation would be fine for me.
tia
Rua
"Madra Rua" <fa...@earthlink.net> wrote in message
news:f3lu9.4863$6F4.4...@newsread2.prod.itd.earthlink.net...
Jon Peltier
Excel's contour plotting capabilities are not very exciting. What you can do
is select your data, run the chart wizard, and select a Contour (colored
regions) or Wireframe Contour (lines only) chart type. The contour charts
are drawn using straight segments only using a childishly simplistic
interpolation routine. In particular, Excel completely messes up saddle
points, connecting the high points as a mountin range, and making the path
from one low point to the other climb up and down the mountain in between. I
find the wireframe-only 2D contour chart next to illegible, because the
contour level segments are indistinguishable from the horizontal and vertical
non-grid gridlines, which cannot be turned off without turning off the
contour level lines as well. I usually use the color filled version, and I
try to make a pretty rainbow effect from low=blue to high=red, because this
is how engineers using real model postprocessers have been trained.
In fact, what I will often do is take my data, inser rows and columns in
between rows and columns of data, and interpolate myself, either by hand or
using formulas. This decreases the granularity of my chart, and allows me to
determine how those in-between points should be treated.
Can you believe I bother? Actually, despite the shortcomings, I have found
these charts useful to help with visualizations. Perhaps by Excel 14 or 15,
Microsoft will have had the chance to improve this potentially powerful
feature.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
In article <R7lu9.4875$6F4.4...@newsread2.prod.itd.earthlink.net>, Madra
Rua said...
Madra Rua
Excellent response!
Have a super weekend :-)
Rua
"Jon Peltier" <jonpe...@yahoo.com> wrote in message
news:OqHpwpKfCHA.2424@tkmsftngp10...
Shannon Jacobs
> Rua -
>
> Excel's contour plotting capabilities are not very exciting. What you can do
> is select your data, run the chart wizard, and select a Contour (colored
> regions) or Wireframe Contour (lines only) chart type. The contour charts
> are drawn using straight segments only using a childishly simplistic
> interpolation routine. In particular, Excel completely messes up saddle
> points, connecting the high points as a mountin range, and making the path
> from one low point to the other climb up and down the mountain in between. I
> find the wireframe-only 2D contour chart next to illegible, because the
> contour level segments are indistinguishable from the horizontal and vertical
> non-grid gridlines, which cannot be turned off without turning off the
> contour level lines as well. I usually use the color filled version, and I
> try to make a pretty rainbow effect from low=blue to high=red, because this
> is how engineers using real model postprocessers have been trained.
>
> In fact, what I will often do is take my data, inser rows and columns in
> between rows and columns of data, and interpolate myself, either by hand or
> using formulas. This decreases the granularity of my chart, and allows me to
> determine how those in-between points should be treated.
>
> Can you believe I bother? Actually, despite the shortcomings, I have found
> these charts useful to help with visualizations. Perhaps by Excel 14 or 15,
> Microsoft will have had the chance to improve this potentially powerful
> feature.
In accord with small world syndrome, I'm looking to do something very
similar to this, but when I try to follow your directions with Excel
XP, I seem to come to a dead end. There aren't any contour chart
options that seem to match what you describe? Am I misinterpreting the
"Chart Wizard"? I thought that is what is triggered from the Insert
menu for Chart? It is possible that some of four possible surface
charts are related, but there is nothing with "contour" that I can
find? Perhaps I need to rearrange my data somehow? I've already tried
a couple of organizations without success...
Perhaps this will help clarify the situation, though I'm not sure I
should post this so early in the search process, except that this post
seems tantalizingly close to what I'm seeking...
I have some graphs of isovalues, and I want to use some of the
well-determined points and interpolate various intermediate values.
The values are actually based on test results of large numbers of
Japanese children studying the Japanese language, and the equivalence
is normalized, so that a value of 3 would represent average reading
ability. The graphs represent the tradeoff of speed versus accuracy,
so that an average studuent who completes the test in 25 minutes with
a perfect score is equated to another average student who finishes the
test in only 10 minutes, but with a comprehension score of 72%.
Boy, that was fuzzy... Let me try again to make this clear. I have an
isovalue line representing average reading ability. The value of this
line is arbitrarily set at 3, and it passes through the points (1,25),
(.8 20), (.75,15), and (.72,10). I have similar data for the 2 curve,
which is one standard deviation better, and for the 4 curve, which is
one standard deviation worse. It would be nice to be able to
approximately regenerate the graph, but what I really want is to be
able to put in a pair of numbers and get back the value of the curve
that point should lie on (including curves that lie between the
existing curves, such as the 2.5 curve for a score half a deviation
above average).
(So far, the only thing I've discovered is some ancient Fortran
interpolation routines, but it's been many years since I've done any
work with Fortran, and I really don't relish the thought.)
Jon Peltier
You're right, the Chart Wizard starts when you choose Chart from the Insert
menu. The Surface chart type in step 1 has four sub types, and in Excel 97
they are called
3-D Surface
Wireframe 3-D Surface
Contour
Wireframe Contour
The contour versions are top views of the 3D versions. The wireframe have
lines between regions of different values; the non-wireframes color in
these regions.
You can easily set up a contour chart to show what you want, as long as
your X,Y values are discretized. Set up the data so the X is in the first
column and the Y in the first row, and the respective Z is in the
intersection of the appropriate X and Y.
There are more rigorous approaches you can use to display iso lines, if you
have them well defined. For example, you could plot iso lines on an XY
Scatter chart, using lines (or smoothed lines) without markers. For each
isoline, you need a column of X values and another of Y values, And these
define points along the contour with a constant Z value. Good for display,
hard to interpolate.
There are decent interpolation routines that have been posted in these
groups. Go to groups.google.com, advanced search, and search the *excel*
groups for interpolate. If it helps, I recall seeing functions posted by
Myrna Larson and by Leo Heuser.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
In article <c5862fb7.02103...@posting.google.com>,
sha...@my-deja.com says...
Shannon Jacobs
<snip of confirmation of my understanding of Chart Wizard and the
appropriate Surface chart types>
Still very tantalized, but...
> You can easily set up a contour chart to show what you want, as long as
> your X,Y values are discretized. Set up the data so the X is in the first
> column and the Y in the first row, and the respective Z is in the
> intersection of the appropriate X and Y.
This is where my understanding is severely broken down and I think I'm
going to strain your obvious expertise... I think that I can arrange
the data as you described it, but when I then attempt to prepare the
chart accordingly, the results are not as expected. The following
description is for the third of the surface charts, the type you
called "contour", which seems to be closest to what I'm seeking to
reproduce... (The fourth type, the wireframe contour is actually what
I have in print, but that's because color printing is more expensive.)
Trying to sort through my confusion, my first column contains values
from 10 to 30 (minutes), the second column ranges from 0.57 to 1.0
(accuracy), and the third column is from 2 to 4 (normalized rankings).
The resulting chart has an x-axis labeled from 1 to 12, and a y-axis
labeled S1, S2, and S3 (on the right side). The legend shows three
groupings, 0-10, 10-20, and 20-30. This makes little sense, so I tried
changing the scaling on the third column, multiplying that arbitrary
value by 10 so the column ranges from 20 to 40. Then the legend is
divided into four segments, the previous three plus 30-40... I
obviously have no idea what is going on here, except that it doesn't
seem to be working in any way that I can map from your description...
> There are more rigorous approaches you can use to display iso lines, if you
> have them well defined. For example, you could plot iso lines on an XY
> Scatter chart, using lines (or smoothed lines) without markers. For each
> isoline, you need a column of X values and another of Y values, And these
> define points along the contour with a constant Z value. Good for display,
> hard to interpolate.
>
> There are decent interpolation routines that have been posted in these
> groups. Go to groups.google.com, advanced search, and search the *excel*
> groups for interpolate. If it helps, I recall seeing functions posted by
> Myrna Larson and by Leo Heuser.
I think I need to work with these parts of your post some more, and
that so far I am heading completely in the wrong direction. So time to
back up to first base?
Trying to make sure my OWN understanding of the problem is clear, I
have the following sets of points associated with isovalue lines at 2,
3, and 4:
2 = {(10,.84),(15,.88),(20,1)}
3 = {(10,.72),(15,.75),(20,.80),(25,1)}
4 = {(10,.57),(15,.60),(20,.66),(25,.77),(30,1)}
The ultimate goal is to define a function that will take two values
and return the number that would be associated with the appropriate
isovalue line passing through that point. An intermediate goal that
should lead towards the ultimate goal would be to approximately
reproduce the original graph based on the set of 12 sampled data
points. (I'd like to limit the number of data points because there are
actually slightly different graphs for various levels.)
Time to unlease the hounds of Google search...
Jon Peltier
I made up a sample workbook with a few contour charts, and I tried to
send it to you. Your email address didn't work. Email me off line
with the correct address and I'll send the file for you to look at.
It may show you how you can still use Excel to get reasonable contour
charts.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
sha...@my-deja.com (Shannon Jacobs) wrote in message news:<c5862fb7.02103...@posting.google.com>...
> jonpe...@yahoo.com (Jon Peltier) wrote in message news:<#kuNtOQgCHA.1652@tkmsftngp11>...
> <snip of confirmation of my understanding of Chart Wizard and the
> appropriate Surface chart types>
>
> Still very tantalized, but...
>
> > You can easily set up a contour chart to show what you want, as long as
> > your X,Y values are discretized. Set up the data so the X is in the first
> > column and the Y in the first row, and the respective Z is in the
> > intersection of the appropriate X and Y.
>
[snip]
...
Relative Zero P/L
If you're looking to produce contour plots you may find Fernando
cinquegrani's grids add-in useful you can find it at
http://www.prodomosua.it/ppage02.html or if you want an english
translation enter the following as the url
http://worldlingo.com/wl/Translate?wl_lp=IT-en&wl_glossary=gl1&wl_documen
ttype=dt10&wl_fl=2&wl_rurl=http%3A%2F%2Fwww.prodomosua.it%2Fppage02.html&
wl_url=http://www.prodomosua.it/ppage02.html&wl_g_table=-3