I'm working on a project that is using some of statistician John Tukey's
methods for Exploratory Data Analysis. Although Excel does not include these
methods, we hope to figure out some way to do them using Excel.

I hope someone can suggest ways to do one or more of the following in Excel
-- or possibly in MicroSoft Access, closely linked to an Excel spreadsheet:

1. A function or macro to carry out an iterative approximation as follows:
given an (unordered) linear array of numbers, y, and a corresponding set of
evenly spaced integers (1,2,...,n) corresponding to each number in the array
a. take the upper and lower thirds of points sorted on their n values,
b. compute the median or mid-mean y values for these upper and lower thirds;
c. find slope for the line connecting the upper and lower y values found in (b);
d. compute estimated y values from (c)
e. compute residual values by subtracting estimates (d) from original y values;
f. use residuals to find another slope and subtract as per a-e
g. iterate until the number of iterations exceeds a specified maximum OR
change in slope is less than a specified real number.

2. A function or macro to carry out an iterative operation as follows:
given a two-way array of numbers (i.e., a specified subset of a table),
a. compute mid-means or medians for each of the rows;
b. subtract row median from each value in each row to give a table of residuals;
c. calculate column mid-means or medians for each column (of residuals from (b);
d. subtract col median from each value in each col to give a table of residuals;
e. continue iterating a-d for specified (even) number of "half-steps" or until
the change in some measure of the over-all set of residuals is less than some
specified value.
**Would the Solver add-in be used for a process like this?**

In each of these cases, we need something that will iterate a certain number of
times or until a certain threshold is reached.

Any suggestions for how to do these kinds of analysis using Excel and/or Access
would be appreciated.

Please respond to as well as to this list.
Thank you,
Gabe Chan

Here are some answers you received in a previous posting:

re 1:
=TRIMMEAN(range,.25)

re 2:
install/activate the Analysis Toolpak addin.
read help on excel's statistical functions,

re 3:
have you tried this with solver ?

this is an NG, not free consultancy ...


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool

==============
J.E. McGimpsey


To include only the middle two quartiles, this should be

=TRIMMEAN(range, 0.5)

In article ,
keepITcool wrote:

=TRIMMEAN(range,.25)

===

--
Regards,
Tom Ogilvy

"Gabe Chan" wrote in message
...
I'm working on a project that is using some of statistician John Tukey's
methods for Exploratory Data Analysis. Although Excel does not include
these
methods, we hope to figure out some way to do them using Excel.

I hope someone can suggest ways to do one or more of the following in
Excel
-- or possibly in MicroSoft Access, closely linked to an Excel
spreadsheet:

1. A function or macro to carry out an iterative approximation as follows:
given an (unordered) linear array of numbers, y, and a corresponding
set of
evenly spaced integers (1,2,...,n) corresponding to each number in the
array
a. take the upper and lower thirds of points sorted on their n values,
b. compute the median or mid-mean y values for these upper and lower
thirds;
c. find slope for the line connecting the upper and lower y values found
in (b);
d. compute estimated y values from (c)
e. compute residual values by subtracting estimates (d) from original y
values;
f. use residuals to find another slope and subtract as per a-e
g. iterate until the number of iterations exceeds a specified maximum OR
change in slope is less than a specified real number.

2. A function or macro to carry out an iterative operation as follows:
given a two-way array of numbers (i.e., a specified subset of a table),
a. compute mid-means or medians for each of the rows;
b. subtract row median from each value in each row to give a table of
residuals;
c. calculate column mid-means or medians for each column (of residuals
from (b);
d. subtract col median from each value in each col to give a table of
residuals;
e. continue iterating a-d for specified (even) number of "half-steps" or
until
the change in some measure of the over-all set of residuals is less than
some
specified value.
**Would the Solver add-in be used for a process like this?**

In each of these cases, we need something that will iterate a certain
number of
times or until a certain threshold is reached.

Any suggestions for how to do these kinds of analysis using Excel and/or
Access
would be appreciated.

Please respond to as well as to this list.
Thank you,
Gabe Chan