Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.charting




Chart with three variables
I've made hundreds of runs with a computer model. With every run, two of the variables are changed, resulting in a new number (third variable). I want to make a chart, with the first variable on the xaxis and the second variable on the yaxis. The point in the chart needs to have, besides the x and yvalue, the value of the third variable. Through the points with the same third variable, a trendline has to be drawn. Because there are several hundreds of model runs, I don't want to sort the data manually on the third variable and make a seperate serie of all data with the same third variable. Is there an easier way to create such a chart?  glasbergenm  glasbergenm's Profile: http://www.excelforum.com/member.php...o&userid=31001 View this thread: http://www.excelforum.com/showthread...hreadid=506690 
#2




Answer: Chart with three variables
Yes, there is an easier way to create such a chart. You can use Excel's builtin PivotChart feature to create a chart with three variables.
Here are the steps to create the chart:
That's it! Your chart should now have all three variables plotted, with a trendline for each value of the third variable.
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.charting




Chart with three variables
Depending on the data, you might be able to turn the data into a pivot
table, with your X values in the row area, the third variable in the column area, and the Y values in your data area. If the third variable is a continuous variable, you may get reasonable results if you group the values in this field.  Jon  Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "glasbergenm" wrote in message ... I've made hundreds of runs with a computer model. With every run, two of the variables are changed, resulting in a new number (third variable). I want to make a chart, with the first variable on the xaxis and the second variable on the yaxis. The point in the chart needs to have, besides the x and yvalue, the value of the third variable. Through the points with the same third variable, a trendline has to be drawn. Because there are several hundreds of model runs, I don't want to sort the data manually on the third variable and make a seperate serie of all data with the same third variable. Is there an easier way to create such a chart?  glasbergenm 
#4
Posted to microsoft.public.excel.charting




Chart with three variables
Dear Jon, I don't see how I can use the pivot table to create the graph. I've put the data and a handmade example for a part of the file as an attachment to this message. I hope this gives a better understanding of what I need. ++ Filename: Clipboard.jpg  Download: http://www.excelforum.com/attachment.php?postid=4288  ++  glasbergenm  glasbergenm's Profile: http://www.excelforum.com/member.php...o&userid=31001 View this thread: http://www.excelforum.com/showthread...hreadid=506690 
#5
Posted to microsoft.public.excel.charting




Chart with three variables
The download link didn't work. ("Invalid Attachment specified", whatever
that means.) You have three columns, X, Y, and Z. I described the arrangement of the pivot table in my previous post in this thread. To create a regular chart from a pivot table, select a blank cell outside the pivot table, and start the chart wizard. In step 1, select the chart type. In step 2, click on the Series tab, then add each series individually, defining the Name (column header in the PT), X Values (left column in the PT), and Y Values (data column in the PT).  Jon  Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "glasbergenm" wrote in message ... Dear Jon, I don't see how I can use the pivot table to create the graph. I've put the data and a handmade example for a part of the file as an attachment to this message. I hope this gives a better understanding of what I need. ++ Filename: Clipboard.jpg  Download: http://www.excelforum.com/attachment.php?postid=4288  ++  glasbergenm 
#6
Posted to microsoft.public.excel.charting




Chart with three variables
Hi,
I posted a response to your post about 6 hours ago, but it hasn't gone through yet; so I am posting it again. For convenience, place your Xvalues in Column B (e.g., B2:B101), Yvalues in Column C (i.e., C2:C101), and Zvalues in Column A (i.e., A2:A101). Select the entire range (A2:C101) and sort by Column A. Make an XYScatter Plot of Yvalues vs Xvalues (Don't join the data points on the plot!). Place one of the possible Zvalues in D2. Select E2:E101, and in the Formula Bar below the Tool Bar enter the following arrayformula and confirm with CTRLSHIFTENTER. =INDIRECT("B"&LARGE(ROW($A$2:$A$101)*($A$2:$A$101= $D$2),COUNTIF($A$2:$A$101,$D$2))&":B"&MAX(ROW($A$2 :$A$101)*($A$2:$A$101=$D$2))) Similarly, select F2:F101, and enter the following arrayformula. =INDIRECT("C"&LARGE(ROW($A$2:$A$101)*($A$2:$A$101= $D$2),COUNTIF($A$2:$A$101,$D$2))&":C"&MAX(ROW($A$2 :$A$101)*($A$2:$A$101=$D$2))) Add a series (Series 2) to the graph you have already created, using E2:E101 as the Xrange and F2:F101 as the Yrange. This plot would correspond to the subset of your X,Ydata that is relevant to the Zvalue you have entered in D2, and hence will overlap a part of the first plot. Add a trendline to Series 2 (and set it up for the equation to show). Now you can manually change the Zvalue, and the Series 2 plot (and the trendline and the trendline equation) will update accordingly. Regards, B. R. Ramachandran "glasbergenm" wrote: I've made hundreds of runs with a computer model. With every run, two of the variables are changed, resulting in a new number (third variable). I want to make a chart, with the first variable on the xaxis and the second variable on the yaxis. The point in the chart needs to have, besides the x and yvalue, the value of the third variable. Through the points with the same third variable, a trendline has to be drawn. Because there are several hundreds of model runs, I don't want to sort the data manually on the third variable and make a seperate serie of all data with the same third variable. Is there an easier way to create such a chart?  glasbergenm  glasbergenm's Profile: http://www.excelforum.com/member.php...o&userid=31001 View this thread: http://www.excelforum.com/showthread...hreadid=506690 
#7
Posted to microsoft.public.excel.charting




Chart with three variables
The Arrayformulas work perfectly, but are not the (complete) solution to my problem: it still means a lot of manual labour to create the trendlines for all of the data (the Zvalue can have about 135 different values). I've tried to make a row with all possible Zvalues and copy the array formula below that row. Instead of cell D2, I've made the formula depend on the Zvalues in the above row. Copying of the array formula, however, doesn't seem as easy as copying an ordinairy formula. When I've found a solution for this, I could use the TREND function (asuming a linear dependency), but this formula has difficulties with blank cells, error values and zero's (every zvalue has a different amount of x and yvalues). What was I thinking when I said "of course I can do this..." :(  glasbergenm  glasbergenm's Profile: http://www.excelforum.com/member.php...o&userid=31001 View this thread: http://www.excelforum.com/showthread...hreadid=506690 
#8
Posted to microsoft.public.excel.charting




Chart with three variables
Hi,
How about the following approach? In D2:D136, enter all possible Zvalues. In E2 and F2, enter the following arrayformulas respectively (CTRLSHIFTENT), autofill the formulas down to E136 and F136 (Note that these formulas are somewhat analogous to the ones I had posted in my previous response, except that I have removed the "INDIRECT" parts and have modified the "$D$2"s as "$D2"s). = "B"&LARGE(ROW($A$2:$A$101)*($A$2:$A$101=$D2),COUNT IF($A$2:$A$101,$D2))&":B"&MAX(ROW($A$2:$A$101)*($A $2:$A$101=$D2)) ="C"&LARGE(ROW($A$2:$A$101)*($A$2:$A$101=$D2),COUN TIF($A$2:$A$101,$D2))&":C"&MAX(ROW($A$2:$A$101)*($ A$2:$A$101=$D2)) The above formulas will return the X and Y ranges (as strings) for each Zvalue inColumn D. You can use the strings in Columns E and F as arguments for functions such as SLOPE, INTERCEPT, TREND, and LINEST. For example, enter the following formulas in G2 and H2 respectively, and autofill them down to G136 and H136. =SLOPE(INDIRECT(F2),INDIRECT(E2)) =INTERCEPT(INDIRECT(F2),INDIRECT(E2)) Regards, B. R. Ramachandran "glasbergenm" wrote: The Arrayformulas work perfectly, but are not the (complete) solution to my problem: it still means a lot of manual labour to create the trendlines for all of the data (the Zvalue can have about 135 different values). I've tried to make a row with all possible Zvalues and copy the array formula below that row. Instead of cell D2, I've made the formula depend on the Zvalues in the above row. Copying of the array formula, however, doesn't seem as easy as copying an ordinairy formula. When I've found a solution for this, I could use the TREND function (asuming a linear dependency), but this formula has difficulties with blank cells, error values and zero's (every zvalue has a different amount of x and yvalues). What was I thinking when I said "of course I can do this..." :(  glasbergenm  glasbergenm's Profile: http://www.excelforum.com/member.php...o&userid=31001 View this thread: http://www.excelforum.com/showthread...hreadid=506690 
#9
Posted to microsoft.public.excel.charting




Chart with three variables
On Wed, 1 Feb 2006, in microsoft.public.excel.charting,
Jon Peltier said: The download link didn't work. ("Invalid Attachment specified", whatever that means.) Jon, click on the "view this thread" link instead, and then click on "Clipboard.jpg" from the page that comes up, or "example.zip" to download the sample dataset. I don't see how I can use the pivot table to create the graph. I've put the data and a handmade example for a part of the file as an attachment to this message. I hope this gives a better understanding of what I need. It looks to me like what you're describing is a contour, or surface, chart. Excel does those. You may then have to manually trace the contours onto separate graphs if you wanted, but I can't see why you'd want to: the contour chart shows them all elegantly in one. However, I failed to get the chart wizard to build a surface chart straight from a pivot chart, because the grey buttons confused the wizard, so I had to create a whole extra table that just duplicated the pivot chart without the "B" and "POC" etc. That let me produce something showing the X and Y axes with realistic scales. See if this helps: http://www.branta.demon.co.uk/science/example2.xls  Del Cotter NB Personal replies to this post will send email to Please send your email to del2 instead 
#10
Posted to microsoft.public.excel.charting




Chart with three variables
The download link didn't work. ("Invalid Attachment specified", whatever that means.) Jon, click on the "view this thread" link instead, and then click on "Clipboard.jpg" from the page that comes up, or "example.zip" to download the sample dataset. I'm not going to open the data file, but I did look at the chart image. I don't see how I can use the pivot table to create the graph. I've put the data and a handmade example for a part of the file as an attachment to this message. I hope this gives a better understanding of what I need. It looks to me like what you're describing is a contour, or surface, chart. Excel does those. You may then have to manually trace the contours onto separate graphs if you wanted, but I can't see why you'd want to: the contour chart shows them all elegantly in one. I don't think it's a contour chart that the OP wants, I think it's a chart with a whole set of trendlines. I can't see any way to do this without making separate trendlines for each subset of the data. This means doing what the OP didn't want to do: "sort the data manually on the third variable and make a seperate serie of all data with the same third variable." I would take this opportunity to learn how VBA might make one's life easier by automatic the constructions of so many data series and trendlines.  Jon  Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Chart two variables with differing values  Charts and Charting in Excel  
Activating a Chart object  Charts and Charting in Excel  
Urgent Chart Assistance  Charts and Charting in Excel  
Urgent Chart Questions  Excel Discussion (Misc queries)  
Urgent Chart Assistance Requested  Excel Discussion (Misc queries) 