Help Math Wizards! (or at least math competents!)

[jakemayer]

I have created a graphed curve in excel based on data I have entered. I now want to take the formula for the graph, and use it as an excel computation where I can plug in X and get Y, or plug in Y to get X. However, when i try to enter in variations of these equations, I end up with just the wrong answers (based on the data that went into the graph to begin with.

The formulas excel gives me are these:

y = -1.1288x3 + 23.209x2 - 166.76x + 430.07 (preferred), or alternately


y = 579.38e-0.574x​

So how can I enter this formula so that I can have it pull x from a cell and spit out the answer for y (and the reverse)?

 

[MrKowz]

This what you need?

Excel Workbook
	A	B	C	D	E
1	y = -1.1288x3 + 23.209x2 - 166.76x + 430.07			y = 579.38e-0.574x
2	X	Y		X	Y
3	0	430.07		0	579.38
4	1	285.3902		1	326.3461
5	2	180.3556		2	183.8203
6	3	108.1934		3	103.5401
7	4	62.1308		4	58.32079
8	5	35.395		5	32.85022
9	6	21.2132		6	18.50348
10	7	12.8126		7	10.42241
11	8	3.4204		8	5.87061
12	9	-13.7362		9	3.306726
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B3	=(-1.1288*(A3^3))+(23.209*(A3^2))-(166.76*A3)+430.07
E3	=579.38*EXP(-0.574*D3)

[/B]

 

[AntMac]

I think the key to this response is that parenthesis never hurt!

Try in the formula bar for where you want the y answer:

=-1.1288*(A2^3) + (23.209 * (A2^2)) - (166.76 * A2) + 430.07

This is using A2 as your cell for x for example

 

[shg]

Rather than read the parameters from the equation on graph, you can use LINEST to get them directly into cells.

Did you intend for your plot to be categorical (a line plot) rather than x-y scatter?

 

[jakemayer]

Wow, you guys are quick!

I am still confused about something, and perhaps it is not an excel problem after all- clearly this does not allow me to plug in a value from the x axis and get the y axis solution? or am i missing something?

 

[MrKowz]

Wow, you guys are quick!

I am still confused about something, and perhaps it is not an excel problem after all- clearly this does not allow me to plug in a value from the x axis and get the y axis solution? or am i missing something?

In the example I posted, it is taking values for the x-axis (the column under X) and returning the value for Y.

 

[jakemayer]

But if i plug in 10,000, would I not then get something around 150? rather than -1126480767170?

 

[MrKowz]

You would not. Something about your formula is not correct:

Excel Workbook
	A	B	C	D	E
1	y = -1.1288x3 + 23.209x2 - 166.76x + 430.07
2	-1.1288*x^3	+23.209*x^2	-166.76*x	+430.07	Total
3	-1.1288E+12	2320900000	-1667600	430.07	-1.12648E+12
Sheet1

Excel 2003

Cell Formulas
Range	Formula
A3	=-1.1288*(10000)^3
B3	=23.209*(10000^2)
C3	=(-166.76)*10000
E3	=SUM(A3:D3)

 

[jakemayer]

Ok. you have hurt my brain. I need to work on this for a while

Thanks for the input!

 

[shg]

When you have a line graph (as you do) and plot a trendline, it is computed for the categorical series {1, 2, 3, ...}

If you want the equation for the actual x values, change the chart to an x-y scatter plot.