Function to tell me what day had the most

[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

[Paul]

"Dominique Feteau" <freekdo...@hotmail.com> wrote in message
news:uLamY9Lx...@TK2MSFTNGP10.phx.gbl...

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.

[Peo Sjoblom]

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

--

Regards,

Peo Sjoblom

"Dominique Feteau" <freekdo...@hotmail.com> wrote in message
news:uLamY9Lx...@TK2MSFTNGP10.phx.gbl...

[Dominique Feteau]

Thanks Paul, it worked like a charm. I really gotta remember how to use the
INDEX function.

Dominique

"Paul" <none> wrote in message news:ehC5MEMx...@TK2MSFTNGP09.phx.gbl...

[Aladin Akyurek]

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

"Dominique Feteau" <freekdo...@hotmail.com> wrote in message
news:uLamY9Lx...@TK2MSFTNGP10.phx.gbl...