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. Compute the midmean of a specified array of cells (either part of a row
or column or a subset of a table). The midmean is the average of the values
between the first quartile (25th percentile) and the third quartile (75th
percentile). Is there any way to create a user defined function that carries
out this process? If there is no way to create a user defined function, would it
be possible to use a macro that would take an array of cells, order the values,
find the quartiles and then find the mean of the values between the quartiles?
The function or macro would need to order the values, find the quartiles, and
then compute the mean of the inner two quartiles. The Excel median function must
do something like this -- i.e., order an array and then find the middle values.
But we can't find other functions that order values as part of their operations.
2. 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.
3. 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.
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. Compute the midmean of a specified array of cells (either part of a row
or column or a subset of a table). The midmean is the average of the values
between the first quartile (25th percentile) and the third quartile (75th
percentile). Is there any way to create a user defined function that carries
out this process? If there is no way to create a user defined function, would it
be possible to use a macro that would take an array of cells, order the values,
find the quartiles and then find the mean of the values between the quartiles?
The function or macro would need to order the values, find the quartiles, and
then compute the mean of the inner two quartiles. The Excel median function must
do something like this -- i.e., order an array and then find the middle values.
But we can't find other functions that order values as part of their operations.
2. 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.
3. 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.
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