Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sorry if this is the wrong place to ask (Meta says it's the right place but you never know) but I'm really struggling with finding a simple answer to this.

I'm using Excel 2011 for Mac, and want it to plot a linear equation.

The equation is ((x*-29.946) + (y*12.689) + (3.39142537117004)) = 0

I've set up the cells as such:

  • Row 1 and column A for labels
  • B2: blank (input X placeholder)
  • C2: blank (input Y placeholder)
  • D2: Constant, -29.946
  • E2: Constant, 12.689
  • F2: Constant, 3.39142537117004
  • G2: =SUM((B2*D2)+(C2*E2)+F2)

What I need is to get Excel to plot a graph of the values of B2 and C2 for which G2 will be 0. I tried setting up something in H2, =G2=0, which contains FALSE, but I don't think this is the right approach.

How do I get Excel to graph this for me? I tried googling, but all I found was stuff on how to solve equation systems (I do need to plot 2 or 3 equations on the same plot, but I don't need to solve the entire system), or how to create a simple graph when you have 2 columns of values.

share|improve this question
1  
Why Excel? Why not just use wolfram alpha? m.wolframalpha.com –  Zoredache Jul 14 '13 at 20:12
2  
The fastest way i can think of would be to solve for y, then have a list of x value (- whatever to + whatever) where the Y value is calculated. ~ y= (29x - 3)/12 –  Pynner Jul 14 '13 at 21:21

1 Answer 1

up vote 3 down vote accepted

There is a very slick way to plot linear and other equations using defined names. The initial setup is a bit tricky, but the end result is flexible and powerful.

  • Begin by creating a Scatter with Smooth Lines chart using a few dummy numbers in the worksheet, something as simple as the number 1 in A1, 2 in A2, 3 in B1, and 4 in B2.

  • Create two defined names for the x and y variables by using the Name Manager in the Formulas tab of the ribbon. For the name "x" (for example), in the "Refers to" input box of the name manager create a set of array constants by entering the formula "={1,2,3,4}" (enter the curly brackets, but not the double-quotes). Do the same for the name "y". (The array constants are dummy amounts that will later be replaced by formulas.

  • Change the series in the chart to refer to the defined names for the equation. Probably the easiest way to do this is to select the line in the chart and then replace the range references in the series equation in the formula bar with the names. Include a reference to the workbook and the sheet in the form shown below when you enter the names; otherwise, Excel won't accept your entry.

  Before:     =SERIES(,Sheet1!$A$1:$B$1,Sheet1!$A$2:$B$2,1)
  You Enter:  =SERIES(,[book1]Sheet1!x,[book1]Sheet1]!y,1)
  After:      =SERIES(,book1!x,book1!y) 
  • Now you enter the basic inputs to the graph

    1. The formula for y as text
    2. The minimum value of x
    3. The maximum value of x
    4. The number of x values

At this point, your sheet might look something like the following, with the initial data you used to create the chart and the basic inputs. (The initial data for the chart are now unused and can be deleted.)

Worksheet with initial chart and basic inputs for x and y

The next step is to redefine the names x and y in the name manager. The idea is to replace the ranges that the names initially pointed to with expressions that will produce the set of x values you defined on the sheet and the y values that result from the application of the equation to those x values.

Go back into the name manager to edit the x and y names.

For the x name, replace the array constants ("={1,2,3,4}") with the formula below. The combination of the ROW and OFFSET functions creates the array {1, 2 ... 20} (for this example), which is then shifted to start at -1 and multiplied by the increment by which each x value increases. The end result is an array of 20 equally spaced points starting at -1 and ending at 1.

=Sheet1!$B$5+(ROW(OFFSET(Sheet1!$B$1,0,0,Sheet1!$B$7,1))-1)*(Sheet1!$B$6-Sheet1!$B$5)/(Sheet1!$B$7-1)

This is an array formula, but no special method is needed to enter it. Excel treats as an array formula any expression entered for a defined name.

For the y name, replace the array constants with the formula

=EVALUATE($B$4)

With the completion of these steps, the graph is ready for any final formatting you need.

final chart

Here is another example chart, where the only change was the equation for y entered into the sheet.

enter image description here

See this link for a more detailed explanation of this technique. A workbook with an example chart can be found here.

share|improve this answer
    
Thanks. In the end I just used Microsoft Mathematics instead though. –  GordonM Jul 21 '13 at 17:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.