G
Guest
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 (e-mail address removed) as well as to this list.
Thank you,
Gabe Chan
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 (e-mail address removed) as well as to this list.
Thank you,
Gabe Chan