+ Reply to Thread
Results 1 to 10 of 10

Make line chart where Y-axis value affected by multiple variables

  1. #1
    Registered User
    Join Date
    07-10-2016
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    5

    Make line chart where Y-axis value affected by multiple variables

    I have the following situation:

    - I have a cell A1 that contains a function where the value changes based on the values of cell B1, C1, D1.
    - I would like to make a line graph where Y-axis is all possible values (0 to 9999) of A1, X-axis is all possible values (0 to 9999) of B1, and I can play with values C1 and D1 to change chart instantly

    Is such a thing possible? Usually I use two sets of series but this is different.
    Thanks!

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,619

    Re: Make line chart where Y-axis value affected by multiple variables

    In A1 you shall write your function to use absolute address of C1 and D1, so in formula use $C$1 and $D$1, while B1 shall be referred to as relative address (just B1).
    Then write all possible values of B in column B so B1, B2, B3 ...
    and copy formula from A1 down.
    You will have set of your X and Y data for the plot (I'd use rather scatterplot connected with lines than standard lineplot) in such situation.

    see sample file
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    07-10-2016
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: Make line chart where Y-axis value affected by multiple variables

    (Dzien dobry!) Thank you for this answer and sample file.

    The problem is B1 is not in the formula A1. A1 is affected by a series of calculations using a large series of different cells and the cell B1 is used near the beginning of these calculations. How can I proceed?

  4. #4
    Forum Contributor
    Join Date
    05-06-2015
    Location
    Sri Lanka
    MS-Off Ver
    2016, 2007
    Posts
    135

    Re: Make line chart where Y-axis value affected by multiple variables

    possible, you can use a slicer, see attached. If you need to select a range of C and D, instead a value, I suggest to use something like a time line.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,824

    Re: Make line chart where Y-axis value affected by multiple variables

    I'm not sure I understand exactly what you are having trouble with. Even if Kaper's sample file does not contain the exact calculation you are working one, the basic principle he illustrates should still apply. If A1 and B1 are calculating correctly, whatever those formulas (and intermediate formulas) look like, creating the chart should be the same. Are you having trouble creating the chart, or are you having trouble putting together the string of calculations that get you from B1 to A1?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Registered User
    Join Date
    07-10-2016
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: Make line chart where Y-axis value affected by multiple variables

    This is what I want to do. Variables are cells B11, B12, B13:
    • Y-axis should be cell I22 (0 to 1000000)
    • X-axis should be cell B11
      I have 20 values for cell B11 ("Variable 1") and 20 corresponding values for cell B12 ("Variable 2") in the table
    • I want to play with cell B13 values and have graph update as a consequence

    Thanks
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,824

    Re: Make line chart where Y-axis value affected by multiple variables

    I think I am beginning to see the heart of the question -- you did not arrange the spreadsheet in a layout that lends itself to easy copying.

    At this point, I think this is how I would proceed:

    1) Move the Table in red off to the right somewhere (or another tab, if you prefer).
    2) Add an "indexing" variable to the existing block of cells (maybe B10 enter 1). This will be used as the block is copied to keep track of which "copy" a given block is.
    3) Change B11 to be an INDEX() function, referring to the red variable 1 column in the red table and to the indexing variable in B10. =index($R$3:$R$22,B10) note the combination of relative and absolute references to make the copying operation work correctly (I've found that this is a BIG part of spreadsheet programming, so become familiar with relative and absolute referencing, if you are not yet familiar).
    4) Copy the main block of cells (A1:M22) and paste down a nice, easy to use number of rows (I would probably do 25 or 30 or 50).
    5) Change the copy of B10 so that it now contains =B10+1 (note, again, the relative reference). The INDEX() function in the copy of B11 should update with the second entry from the table. link the copies of B13 (and other cells you want to be able to modify) so that they will change throughout the sheet as you change them.
    6) Copy the modified block and paste as many copies down the sheet as you need.
    7) In a helper column adjacent to the red table, calculate the rows that will correspond to the copies of I22 (22, 72, 122, etc, if you copied it down 50 rows).
    8) Use an INDEX() or OFFSET() function (I prefer INDEX(), myself) and the helper column to extract the results from I22 and its copies.

  8. #8
    Registered User
    Join Date
    07-10-2016
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: Make line chart where Y-axis value affected by multiple variables

    Yes, that worked a treat. Thank you very much for taking the time to write it all up.

    I wasn't sure how to do step 8 so I entered the "=i22" "=i72" etc. values manually.

    Is there anything I could have changed in this spreadsheet to make my life easier? Based on your comments and this solution, I believe I may have complicated things...

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,824

    Re: Make line chart where Y-axis value affected by multiple variables

    One of the key "advantages" of a spreadsheet as a programming language is its ability to easily repeat the same calculation multiple times. One of the big "tools" for this is copying. The idea is, as you are entering your calculations, have in mind what is going to happen when you want to make multiple copies of this calculation. In your case, your "input" cell and your "output" cell for each copy of the calculation was buried in the middle of the block of cells. One thing that could have made this easier is to arrange the calculation so that your input is alone in the leftmost column, and your output is in the rightmost column. With this change, you may not need the "red" table to summarize the results, since you can create the chart from the "input" and "output" columns directly. If you do decide that you want the red summary table anyway, it can be easier to create the summary table using lookup functions.

    I'm sure there are a lot of possible suggestions. The main thing I have learned is to always keep in mind what will happen when I copy a sequence of calculations. Even if I originally did not think I would need to copy something, so often I find some reason to wish I had made something easier to copy.

  10. #10
    Registered User
    Join Date
    07-10-2016
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: Make line chart where Y-axis value affected by multiple variables

    Thanks again for your help and valuable advice. Will use your suggestions as I work to improve my skills.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 11-20-2015, 12:50 PM
  2. Need help on Line Chart with Multiple Variables
    By aliwaii in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-30-2011, 04:34 PM
  3. How do I make a line-column 2-axis chart in Excel 2007?
    By shadestreet in forum Excel General
    Replies: 2
    Last Post: 04-09-2009, 09:35 PM
  4. Line chart with multiple scales/units along an axis...
    By JohanD in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-19-2006, 01:00 AM
  5. Replies: 2
    Last Post: 09-22-2005, 06:57 AM
  6. Replies: 2
    Last Post: 08-12-2005, 08:05 AM
  7. make line in chart start at y axis
    By Tone in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-05-2005, 05:06 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1