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

 

[jakemayer]

Clearly I am missing something. If I plug the x axis into the formula, I should get the Y value, yes? Is the graph not corresponding to the formula, or am I just not doing it right?

 

[jakemayer]

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.

AHA! So I am doing it wrong perhaps.. What I want is the predicted x y values based on the graphed line. still x-y scatter?

 

[AntMac]

Ah, here's where the math portion comes in.

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


y = 579.38e-0.574x​

If you use the exponential equation your expected outcome I can see happening without plugging in the numbers. You should never get a negative number with the exponential equation the way it is. The first equation (that you called preferred so everyone used for their answer) you have two negative terms that will dominate the higher about 23.209 you go in x values. So, it will eventually get to negative numbers. So, moral of the story is what does the equation represent and which equation fits the mole better.

 

[jakemayer]

Ah, here's where the math portion comes in.

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


y = 579.38e-0.574x​

If you use the exponential equation your expected outcome I can see happening without plugging in the numbers. You should never get a negative number with the exponential equation the way it is. The first equation (that you called preferred so everyone used for their answer) you have two negative terms that will dominate the higher about 23.209 you go in x values. So, it will eventually get to negative numbers. So, moral of the story is what does the equation represent and which equation fits the mole better.

OK. let's start over. Sorry for my lack of actual math knowledge here (i admit, I zoned out once hitting Algebra 2). The x-axis represents the sales rank of a product (lower being better). The Y axis is the number if units sold to the corresponding ranking. I just chose the graph function that mirrored the sample data the best, perhaps this was not the best way to select!

 

[shg]

You have a line graph. When you do that, Excel considers the x values to be names, not numbers. You could change 5000 to "Alan", 1000 to "Bob", 20000 to "Chuck", ... and see the same result.

Notice that the x values are evenly spaced along the axis, though the numbers certainly are not.

When you add a trendline to a line chart, it uses 1 for the first x value, 2 for the second, 3 for the third, ...

Change the chart to an x-y scatter plot and then use a power series trendline, and you'll see that the equation of the line as

y = 271764*x^(-0.788)

 

[jakemayer]

Brilliant. Problem solved, despite my ignorance. You all rock!