Statistical functions in an array

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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)
 
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, ...
 
Thanks your formula did the trick, and for being willing to help those of us
that are excel challenged.
 
Back
Top