excel and graphs

[pdaunt]

heres the setup i have a graph in excel with an x axis in reverse logarithmic scale. im trying reference x values from known y values from the graph. ive tried using FORCAST and other such commands to do it but it seems like there using a linear trend line to find the values, so its off a bit. so my question is how do i reference a graph

 

[WildHorse]

You must have some equation that describes the line that is graphed through your data points.

(Maybe you have a formula for a least squares fitted line ?)

So use simple algebra to flip your equation around, so you can find X.

Work your equation around so that X is alone on one side of the =, and everything else on the other side.

So then just plug your known Y values into the modified equation, to find corresponding X values. Easy!

edit: typo fixes

 

[pdaunt]

ok ill give that a throw but i was really hoping to set up a spreadsheet that was used as a calculator, so i would put in data (the graphing step is a middle step) and it would automatically spit out answers. but if a second input is neccasary then o well. i thought excel would be smart enough to do an easy task like such.

 

[WildHorse]

Excel probably IS smart enough,

but I'm not.


New hope for you: look around in here.

 

[edcarman]

I would look at using the LINEST and LOGEST array formulas.

I haven't used LOGEST before, but I I have used LINEST to return coefficients and regression statistics for up to a 6th order polynomial fit (y = m1x + m2x^2 + ... +m6x^6 + b) and I believe it could go higher.

LINEST is particulary useful if you can manipulate your data into a form that can be expressed as a polynomial e.g. y = a^x is exponential, but can be expressed as logy = xloga which is a linear relationship between logy and x (although in this case it looks like you could use LOGEST).

You may have already encountered this, but beware of using fitted curves to (to a lesser extent) interpolate and (to a much greater extent) extrapolate data.
For example, if you've got only 7 values, a 6th order polynomial will give a perfect fit while given predicted values that are, in all likelihood, completely meaningless.

I have also encountered problems with combining predictions from separate fitted curves. I fitted a polynomial to some air property data for kinematic viscosity and diffusivity. The curve gave good predictions for individual values within each range, but gave inaccurate results when I divided derived viscosity values by derived diffusivity values.

 

[pdaunt]

ok ill take a look at that but i did figure out anther way to do it. i set up two if then statements to find were my wanted x accured in the set of data then when all was true i then used the forecast (just for when the x value accured) to find the exact value.

column E was the x values and column G was the y values and i was finding and entering a known x as .6

=IF(E5<0.6,IF(E4>0.6,FORECAST(0.6,G4:G5,E4:E5),""),"")

hope that helps others.