AutoFilter Top 10 - Incorrect Answer

[Rebecca_SUNY]

I am trying to describe AutoFilter Top 10 to someone but my example does not
create a list of 10 items....there are 11 items.

My data is a list of 100 students with Gender, Favorite Color and Average
number of hours of sleep on the weekdays. I have only included the first 20
here as an example.

If I filter this list for the top 10 items, I actually get 11 items in my
list. If I filter for the top 15 items - I get 18 items.

Why does this happen? (intuitively I know it has something to do with the
way Excel calculates the “custom†filter off the top 10 dialog box – Top 10
is less than or equal to 10 and Top 15 is less than or equal to 9, but I
can’t explain why/how Excel does this.)

Gender Color Sleep/wkday
M blue 8
F yellow 8
F pink 11
F red 11
M blue 9
M green 10
M blue 10
F red 11
M green 9
M blue 10
F yellow 11
M purple 9
F yellow 9
F blue 11
F blue 9
M blue 9
M orange 10
M blue 9
M pink 11
F black 11

 

[JudithJubilee]

Hi Rebecca,

You are getting 11 from the list because Excel cannot differentiate between
the 11 and 10 hour lines. There are 7 @ 11, and 4 @ 10.

If your numbers were all different you would get 10 exactly.

Judith