Statistical functions in an array

[Guest]

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.

 

[T. Valko]

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)

 

[Guest]

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, ...

 

[Guest]

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