ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Determine Most/Least Frequent Date from List (https://www.excelbanter.com/excel-worksheet-functions/238362-determine-most-least-frequent-date-list.html)

bpc

Determine Most/Least Frequent Date from List
 
I have a list of events (Column A) and dates of events (Column B). I would
like to determine the most frequent and least frequent dates from the list.
No VBA please.

My current partial solution includes a pivot table in an adjacent worksheet
(sorted in descending order) which performs a count of the dates. I then
reference the "top cell" of the pivot table to determine the maximum date as
this cell location does not change. How can I reference lowest or bottom
number as that cell reference will regularly change?
Or, am I making this far to difficult?
Thanks in advance,
BPC



Mike H

Determine Most/Least Frequent Date from List
 
Hi,


=MODE(A1:A20)
will find the most frequent date

How can I reference lowest or bottom
number as that cell reference will regularly change?
Or, am I making this far to difficult?


Not too difficult but not clear exaclty what you want.

Mike

"bpc" wrote:

I have a list of events (Column A) and dates of events (Column B). I would
like to determine the most frequent and least frequent dates from the list.
No VBA please.

My current partial solution includes a pivot table in an adjacent worksheet
(sorted in descending order) which performs a count of the dates. I then
reference the "top cell" of the pivot table to determine the maximum date as
this cell location does not change. How can I reference lowest or bottom
number as that cell reference will regularly change?
Or, am I making this far to difficult?
Thanks in advance,
BPC



bpc

Determine Most/Least Frequent Date from List
 
Mike,

Thanks, the mode function (inserted in C1) will work, for the date
(01/01/09) with the highest number off calls. Now I am looking for a
calculation for c2 to return the date with the lowest number of calls
(01/05/09 in my example).
A B C
001 01/01/09 Day w/ highest number of calls
002 01/01/09 Day w/ lowest number of calls
003 01/03/09
004 01/03/09
005 01/05/09

Thanks,



"Mike H" wrote:

Hi,


=MODE(A1:A20)
will find the most frequent date

How can I reference lowest or bottom
number as that cell reference will regularly change?
Or, am I making this far to difficult?


Not too difficult but not clear exaclty what you want.

Mike

"bpc" wrote:

I have a list of events (Column A) and dates of events (Column B). I would
like to determine the most frequent and least frequent dates from the list.
No VBA please.

My current partial solution includes a pivot table in an adjacent worksheet
(sorted in descending order) which performs a count of the dates. I then
reference the "top cell" of the pivot table to determine the maximum date as
this cell location does not change. How can I reference lowest or bottom
number as that cell reference will regularly change?
Or, am I making this far to difficult?
Thanks in advance,
BPC




All times are GMT +1. The time now is 03:54 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com