Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
line of best fit/reading values from graphs
i have excel 2003 and am trying to create graphs for an electronics lab write
up. I have entered information into a table and created scatter diagrams etc but i want to put on a line of best fit. The trendline function does not work well enough for what i want, and i remember there being an option for a line of best fit on an older version of the program. is there a way to do it? The reason i am asking is because i want to read off values from my graph, i.e. on a voltage vs frequency graph i want to know what the frequency would theoretically be at 0.707 volts. if i can not create a best fit line, is there a way to do this?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
line of best fit/reading values from graphs
they're not linear or polynomial. some are like exponential but upside down
(yet not logarithmic, dunno what they're called). some are like the shape of a black body curve if you ever did physics alevel. they're weird shapes that can't be mapped onto a 'normal' function. what i really need to know is how to get excel to read x values for a given y value "Bernard Liengme" wrote: You did not tell if the fit is linear or not You can use LINEST to fit to linear, polynomial, log etc See www.stfx.ca/people/bliengme/ExcelTips and www.tushar-mehta.com -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Becky Hutchinson" <Becky wrote in message ... i have excel 2003 and am trying to create graphs for an electronics lab write up. I have entered information into a table and created scatter diagrams etc but i want to put on a line of best fit. The trendline function does not work well enough for what i want, and i remember there being an option for a line of best fit on an older version of the program. is there a way to do it? The reason i am asking is because i want to read off values from my graph, i.e. on a voltage vs frequency graph i want to know what the frequency would theoretically be at 0.707 volts. if i can not create a best fit line, is there a way to do this?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
line of best fit/reading values from graphs
A couple of ideas: 1) Are your data spaced closely enough to use interpolation instead of curve fitting to get reasonable results? Excel doesn't have a built in interpolation function (some other spreadsheets have built in linear interpolation functions if you're not tied to Excell), but it can be done in Excel. Either write a UDF for it, or work out the lookup portion using INDEX(MATCH()) then interpolate on the located interval. 2) If you insist on using a curve fitting approach, you've got to come up with some kind of function that will represent your data. Trendlines and LINEST really only work on linear parameter (or equation where the parameters can be linearized), but there are other functions that don't use linear parameters for which the parameters can be found using a numerical approach like Solver. For example, you mention that some of your data look like a blackbody radiation curve. Been a long time since I dealt with that stuff, but a quick Google search found that a blackbody curve is an equation of the form y=(Ax^-5)/(B^1/x-1) where A and B are constants (B is a function of temperature). In your case, I would just treat A and B as parameters. Can you come up with a few equations that will fit your data? -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=496153 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
line of best fit/reading values from graphs
A curve fit with Solver would be possible if you have an idea what function
you wish to fit the data to. Contact me privately if you wish to explore this idea. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Becky Hutchinson" wrote in message ... they're not linear or polynomial. some are like exponential but upside down (yet not logarithmic, dunno what they're called). some are like the shape of a black body curve if you ever did physics alevel. they're weird shapes that can't be mapped onto a 'normal' function. what i really need to know is how to get excel to read x values for a given y value "Bernard Liengme" wrote: You did not tell if the fit is linear or not You can use LINEST to fit to linear, polynomial, log etc See www.stfx.ca/people/bliengme/ExcelTips and www.tushar-mehta.com -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Becky Hutchinson" <Becky wrote in message ... i have excel 2003 and am trying to create graphs for an electronics lab write up. I have entered information into a table and created scatter diagrams etc but i want to put on a line of best fit. The trendline function does not work well enough for what i want, and i remember there being an option for a line of best fit on an older version of the program. is there a way to do it? The reason i am asking is because i want to read off values from my graph, i.e. on a voltage vs frequency graph i want to know what the frequency would theoretically be at 0.707 volts. if i can not create a best fit line, is there a way to do this?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Producing line and bar graphs on same axis | Charts and Charting in Excel | |||
Line Graphs - How do I plot N/As? | Charts and Charting in Excel | |||
Removing 0 or none 0 values from graphs | Charts and Charting in Excel | |||
graphs showing unwanted zero values | Excel Discussion (Misc queries) | |||
search after values | Excel Discussion (Misc queries) |