Results 1 to 9 of 9

Thread: Using chart with multiple variables in Excel

  1. #1
    Join Date
    Oct 2011
    Posts
    89

    Using chart with multiple variables in Excel

    I have 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 x-axis and the second variable on the y-axis. The point in the chart needs to have, besides the x and y-value, 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 separate series of all data with the same third variable. Is there an easier way to create such a chart.

  2. #2
    Join Date
    Aug 2011
    Posts
    540

    Re: Using chart with multiple variables in Excel

    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.

  3. #3
    Join Date
    Aug 2011
    Posts
    695

    Re: Using chart with multiple variables in Excel

    I don't see how I can use the pivot table to create the graph. I have put the data and a hand-made example for a part of the file as an attachment to this message. I hope this gives a better understanding of what I need.

  4. #4
    Join Date
    Aug 2011
    Posts
    566

    Re: Using chart with multiple variables in Excel

    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).

  5. #5
    Join Date
    Jul 2011
    Posts
    623

    Re: Using chart with multiple variables in Excel

    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 X-values in Column B (e.g., B2:B101), Y-values in Column C (i.e., C2:C101), and Z-values in Column A (i.e., A2:A101). Select the entire range (A2:C101) and sort by Column A. Make an XY-Scatter Plot of Y-values vs X-values (Don't join the data points on the plot!). Place one of the possible Z-values in D2. Select E2:E101, and in the Formula Bar below the Tool Bar enter the following array-formula and confirm with CTRL-SHIFT-ENTER. =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 array-formula. =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 X-range and F2:F101 as the Y-range. This plot would correspond to the subset of your X,Y-data that is relevant to the Z-value 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 Z-value, and the Series 2 plot (and the trendline and the trendline equation) will update accordingly.

  6. #6
    Join Date
    Jul 2011
    Posts
    634

    Re: Using chart with multiple variables in Excel

    The Array-formulas work perfectly, but are not the (complete) solution to my problem: it still means a lot of manual labor to create the trendlines for all of the data (the Z-value can have about 135 different values). I've tried to make a row with all possible Z-values and copy the array formula below that row. Instead of cell D2, I have made the formula depend on the Z-values in the above row. Copying of the array formula, however, doesn't seem as easy as copying an ordinary formula. When I've found a solution for this, I could use the TREND function (assuming a linear dependency), but this formula has difficulties with blank cells, error values and zero's (every z-value has a different amount of x and y-values).

  7. #7
    Join Date
    Jul 2011
    Posts
    640

    Re: Using chart with multiple variables in Excel

    In D2136, enter all possible Z-values. In E2 and F2, enter the following array-formulas respectively (CTRL-SHIFT-ENT), 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 Z-value 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))

  8. #8
    Join Date
    Jun 2011
    Posts
    635

    Re: Using chart with multiple variables in Excel

    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.

  9. #9
    Join Date
    Jun 2011
    Posts
    487

    Re: Using chart with multiple variables in Excel

    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 separate series 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.

Similar Threads

  1. How to disable excel for taking empty cell in Excel chart
    By Hache hi in forum MS Office Support
    Replies: 2
    Last Post: 25-02-2012, 12:30 PM
  2. There is no data in Excel Pie Chart
    By Tarani in forum MS Office Support
    Replies: 2
    Last Post: 25-02-2012, 12:27 PM
  3. How to start Excel chart at zero
    By Hameeda-K in forum Windows Software
    Replies: 2
    Last Post: 05-01-2012, 04:38 PM
  4. Multiple milestones in same horizontal line on Gantt chart
    By mbbackus in forum Microsoft Project
    Replies: 6
    Last Post: 21-07-2011, 06:45 PM
  5. Excel Function and Excel Chart Colors
    By Samarth in forum Windows Software
    Replies: 5
    Last Post: 13-01-2010, 08:16 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,667,935.38218 seconds with 17 queries