most used value

[Haralambus]

hi,
I have data in 1300 rows and 8 columns (all numbers). How can I filter out
most used x values?

thanks

 

[Glenn]

hi,
I have data in 1300 rows and 8 columns (all numbers). How can I filter out
most used x values?

thanks

Assuming your data is in A1:H1300 and nothing in J1, put the following array
formula (commit with CTRL+SHIFT+ENTER) in J2 and copy down x rows:

=MODE(IF(COUNTIF($J$1:J1,$A$1:$H$1300)=0,$A$1:$H$1300))

These are the most used values. Not sure what you want to do from there...

 

[Shane Devenshire]

Hi,

You have a problem here in your defintion of the problem, consider this
small example:

1 2 3
4 1 5
6 7 1
8 3 9

Do you want to hide the first three rows because 1 occures the most often
and is found on all three rows? You can't filter single cells, you can hide
entire rows or columns but not cells. You can suppress the display of the
value in a cell based on a condition, but that cell is still there and its
value remains.

The simplest way to find the most frequent items is
=MODE(A1:H1300)

However, Excel does not handle bimodal and trimodal distributions, so how do
you want to handle situations in which two different numbers are the most
frequent occuring the same number of times?

 

[Tom]

hi,
I have data in 1300 rows and 8 columns (all numbers). How can I filter out
most used x values?

thanks

data analysis -> histogram