I have 4400+ lines of historical returns data. I have my columns set up as
the date in column "A" and then the return for that date in column "B". What
I want to accomplish is to set the parameters to search column "A" for a
beginning date (say this date is stated in cell D2), and then search column
"A" again for and ending date (say this date is stated in cell D3). What I
want for a return in my value cell (answer) is the mode from the historical
return information in column "B" from the dates between these two dates. Then
in other cells I want to find the max, min, median, and standard deviation
with the same date parameters a beginning date and an ending date.

In a nut shell, I am trying to get the statistical information by limiting
the information searched to only the return data between two dates I have
selected.
--
Thanks, Kate

Try these array formulas** :

=MODE(IF((A2:A20=D2)*(A2:A20<=D3),B2:B20))

Us the same syntax for each of the functions:

=MAX(IF(....................)
=MIN(IF(.....................)
=STDEV(IF(................)
=MEDIAN(IF(.............)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Kate" wrote in message
...
I have 4400+ lines of historical returns data. I have my columns set up as
the date in column "A" and then the return for that date in column "B".
What
I want to accomplish is to set the parameters to search column "A" for a
beginning date (say this date is stated in cell D2), and then search
column
"A" again for and ending date (say this date is stated in cell D3). What
I
want for a return in my value cell (answer) is the mode from the
historical
return information in column "B" from the dates between these two dates.
Then
in other cells I want to find the max, min, median, and standard deviation
with the same date parameters a beginning date and an ending date.

In a nut shell, I am trying to get the statistical information by limiting
the information searched to only the return data between two dates I have
selected.
--
Thanks, Kate

Think you could use something like this, array-entered** in say, D4:
=MEDIAN(IF((A$2:A$4400=D2)*(A$2:A$4400<=D3),B$2:B $4400))

**"Array-entered" means to press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER.

Frame it up likewise for max, min, ...
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kate" wrote:
I have 4400+ lines of historical returns data. I have my columns set up as
the date in column "A" and then the return for that date in column "B". What
I want to accomplish is to set the parameters to search column "A" for a
beginning date (say this date is stated in cell D2), and then search column
"A" again for and ending date (say this date is stated in cell D3). What I
want for a return in my value cell (answer) is the mode from the historical
return information in column "B" from the dates between these two dates. Then
in other cells I want to find the max, min, median, and standard deviation
with the same date parameters a beginning date and an ending date.

In a nut shell, I am trying to get the statistical information by limiting
the information searched to only the return data between two dates I have
selected.
--
Thanks, Kate

Thanks your formula did the trick, and for being willing to help those of us
that are excel challenged.
--
Thanks, Kate