Function to tell me what day had the most

  • Thread starter Thread starter Dominique Feteau
  • Start date Start date
D

Dominique Feteau

I have a small problem. I need a way to find out which day of the week had
the most entries.

The table looks like this:


Day Entries
Mon 5
Tues 4
Wed 6
Thurs 3
Fri 4

by just looking at this, I know that Monday has the most, but the entries
column are functions themselves and will change automatically from another
worksheet when new data is entered. How can I set it up so I can have one
cell telling me which out of those five days had the most entries. Also,
what about ties?

Dominique
 
Dominique Feteau said:
I have a small problem. I need a way to find out which day of the week had
the most entries.

The table looks like this:


Day Entries
Mon 5
Tues 4
Wed 6
Thurs 3
Fri 4

by just looking at this, I know that Monday has the most, but the entries
column are functions themselves and will change automatically from another
worksheet when new data is entered. How can I set it up so I can have one
cell telling me which out of those five days had the most entries. Also,
what about ties?

Dominique
Click to expand...

Well, if the above data were in A1:B5, this formula
=INDEX(A1:A5,MATCH(MAX(B1:B5),B1:B5,0))
would return "Wed".
(It looks to me as though this had more entries than Mon!)
If there is a tie, it will return the earlier day.
 
Use the autofilter, if not a formula might look like

=INDEX(Day_Range,MATCH(MAX(Entries_Range),Entries_Range,0))

You might have to use other formulas in case there are multiple most days
 
Thanks Paul, it worked like a charm. I really gotta remember how to use the
INDEX function.

Dominique
 
Let A2:B7 house the following sample:

{"Day","Entries";"Mon",5;"Tues",4;"Wed",5;"Thurs",3;"Fri",4}

In C2 enter: Rank

In C3 enter & copy down:

=RANK(B3,$B$3:$B$7)+COUNTIF($B$3:B3,B3)-1

In D1 control+shift+enter, that is, confirm the formula with
control+shift+enter instead of just with enter:

=MAX(IF(INDEX(B2:B6,MATCH(E1,C2:C6,0))=B2:B6,C2:C6))-E1

In E1 enter: 1 (Top N where N = 1)

In D2 enter: Day

In D3 enter:

=IF(E3<>"",INDEX($A$3:$A$7,MATCH(ROW()-ROW($D$3)+1,$C$3:$C$7,0)),"")

Note the correspondence between the formula cell and the ROW($D$3) bit in
the formula.

In E2 enter: Entries

In E3 enter & copy down:

=IF(ROW()-ROW($E$3)+1<=$E$1+$D$1,INDEX($B$3:$B$7,MATCH(ROW()-ROW($E$3)+1,$C$
3:$C$7,0)),"")

This self-adjusting Top N system of formulas will return as results:

Mon
Wed
 
Back
Top