Calculate mode based on criteria

[Guest]

My data looks like this:

Store# Date Freq. of Dates
1 10/2/2006 2
1 10/2/2006 2
2 10/2/2006 2
2 10/1/2006 1
2 10/2/2006 2
3 10/5/2006 3
3 10/5/2006 3
3 10/3/2006 1
3 10/5/2006 3

I want to insert a new column that will pull in the date for the max of
Freq. of Dates for a given store, which in essence gives the mode date for
each store. I would like my data to look like this.

Store# Date Freq. of Dates Mode Date
1 10/2/2006 2
10/2/2006
1 10/2/2006 2
10/2/2006
2 10/2/2006 2
10/2/2006
2 10/1/2006 1
10/2/2006
2 10/2/2006 2
10/2/2006
3 10/5/2006 3
10/5/2006
3 10/5/2006 3
10/5/2006
3 10/3/2006 1
10/5/2006
3 10/5/2006 3
10/5/2006

I've been scratching my head about this one for awhile and can't come up
with a solution. Can anyone help? Perhaps there's an easier way to get the
end result I'm looking for without even using the Freq of Dates field. What
I'm really after is the mode Date.

Thanks

 

[T. Valko]

Try this:

Store #'s in A2:A10, dates in B2:B10-

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=MODE(IF(A$2:A$10=A2,B$2:B$10))

Copied down

Note: if there is no mode the formula returns #N/A.

Biff