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

On Thu, 7 Sep 2006, in microsoft.public.excel.charting,
Ahab said:

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.

--
Del Cotter
NB Personal replies to this post will send email to ,
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?

Ahab wrote:
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?


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

"Ahab" wrote:

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

On Fri, 8 Sep 2006, in microsoft.public.excel.charting,
Ahab said:

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?

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.

--
Del Cotter
NB Personal replies to this post will send email to ,
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!