 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Charting / September 2006Extracting Data from X, Y charts
I am an audiologist working for a London hospital and am interested in trying to computerise some of the analyses of tests we currently work out by hand. One test involves playing speech at different intensity levels and then plotting the patients performance as a series of percentages on a graph. E.g. at 10dB 20%, 20dB 40%, 45dB 60% etc. A line of best fit is then drawn and the resulting graph used to obtain data to put through some formulae. I can quite easily use Excel to plot an appropriate chart and add a suitable trendline but I am at a loss ias to how to get excel to "read" data from that trendline. For example one of the formulae we use is to determine what we call the speech reception threshold, the intensity of sound at which the Pt can correctly repeat speech 50% of the time. This intensity rarely corresponds to an actual intensity that has been presented but is simply obtained by tracing across from 50% on the Y axis until the trendline is met and then reading down from that point to the corresponding intensity level on the x axis. I have no idea how I can get Excel to go through this process or even if it can! I would be extremely grateful for any help as now I've started doing this I'd like to see it through to completion. Thank You AB >A line of best fit is then drawn and the resulting graph used to obtain data >to put through some formulae. I can quite easily use Excel to plot an >appropriate chart and add a suitable trendline but I am at a loss as to how >to get excel to "read" data from that trendline. As is so often the case, this is not really an appropriate thing to ask the charting engine to do. Instead, try experimenting with the SLOPE() and INTERCEPT() functions in the spreadsheet itself to get them to calculate the figure you are looking for.  SignatureDel Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. Thanks for your advice, I should have clarified that I'm not necessarily expecting the charting engine itself to complete this funtuion for me. If within the spreadsheet there is a function that will enable me to do this then I'll be very happy. I havn't looked at any of the two functions that you mentioned yet but I will give thema go and hopefully be able get at the data I need. Thanks Again AB I've had a look at the intercept and slope functions and neither seems to do what I require, Intercept appears to calculate the point at which a line crosses the Y axis and Slope gives ... well the slope of the line. All I want Excel to do is when I input a Y value, for the software to tell me what the corrosponding x value is through referencing the chart and trendline that it has drawn up from the provided dataset. Surely somewhere Excel will let me do this....won't it? > I've had a look at the intercept and slope functions and neither seems to do > what I require, Intercept appears to calculate the point at which a line [quoted text clipped - 4 lines] > > Surely somewhere Excel will let me do this....won't it? y = mx + b therefore x = ( y- b)/m b=INTERCEPT() m = SLOPE() Will this do it? Thank you for your reply, and thank yuo to del whjo replied earlier, using the slope and intercept functions as suggested I can indeed get the figure I'm looking for. There is a problem however.... These funtions only seem to work only if the trendline is based on linear regression, unfortunately the data from the test needs to be plotted in a different way, I've found so far that polynomial regression seems to give the best results and draws a trend that looks closest to what we do by hand. In some cases a speech curve will experience what we call "rollover" i.e. the ability of the patient to hear speech will deterioriate (rather than increase) as the intensity of speech sounds are raised. This effect is characteristic of neural lesions and back in the day before MRI used to be used as part of diagnostic assessments of brain tumors amongst other things. Its very important then that any trendline that is drawn from the data will accurately display this rollover... My question is then, is there a way I can use these functions or any others to get the results from the x axis with a different sort of trendline that can reflect the above mentioned rollover effect? Thanks again for your help The LINEST and TREND worksheet functions can fit polynomial models. The appropriate syntax is discussed at http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm You can use Solver to find an x value for which TREND achieves the desired value. Jerry > Thank you for your reply, and thank yuo to del whjo replied earlier, using > the slope and intercept functions as suggested I can indeed get the figure [quoted text clipped - 16 lines] > Thanks again for your help > >In some cases a speech curve will experience what we call "rollover" i.e. >the ability of the patient to hear speech will deterioriate (rather than [quoted text clipped - 6 lines] >different sort of trendline that can reflect the above mentioned rollover >effect? A polynomial function might do it to your satisfaction, but check the literature to see if there are models for the resulting curves in hearing acuity. It's best to follow a model if there's one available. In general there is a way to use Excel to get a curve fitted for any function you can think of. I confess I never have used the TREND() or LINEST() functions myself; I always preferred to use a roll-your-own version which I fitted using the Goal Seek add-in under Tools. This may mean I have unrealistic expectations of the built-in Excel curve-fitting functions. SignatureDel Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. Thanks for the replies again Funnily enough I can find nothing in the literature dealing with how the curve should be drawn, pretty much every text assumes the line will be drawn by hand to a "best fit curve". I'll give a go to the linest and trend functions over the next few days and report back. I must say though I was a little dismayed by the formula for calculating polynomial regression, I rather thought before I got started on this that working out how to get excel to computerise the analysis of speech audios would be pretty straightforward and take me an hour or two, instead I've opend the lid of some terrible pandoras box of statistics! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.