Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Election 2004 Project -- iterative operations
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iterative Equation | Excel Discussion (Misc queries) | |||
Iterative process, | Excel Discussion (Misc queries) | |||
I can't format 01/19/2004 to read January 19, 2004, please help!! | Excel Discussion (Misc queries) | |||
Iterative Macro | Excel Programming | |||
Help: Election 2004 Project -- Iterative Operations, midmean | Excel Programming |