I have a full sheet which contains a lot of raw data. It will not however go beyond 500 x 500 cells so I use the range "A1:SF500" as a range named All.

I am trying to get the top ten values, numerical only, there are some text values (only "prog" and "autre") but I do not want them to count (as they will come back pretty often.

So my data is in a sheet called "Raw Data" and I want the top ten will be in an other sheet named "Stats Data" in the range "J42:J51".

I know about the Mode() function and I have tried something like this:

In J42 (most common value) is : =Mode(All)

in J23 (second most common value) is : =MODE(SI(All<J42;All;""))

When entering the J23 formula if it's not in array mode I get the same value as J42 and if it is in array mode I get 0.

I also have another problem in that I would like that count to ignore the values "0" and "299990".

If someone could help me I would greatly appreciate it, this is for statistics purposes in regarding to the use pharmacists make of their tech support (me).

Thanks

Hi,

Am Wed, 12 Aug 2015 13:30:41 -0700 (PDT) schrieb
:

I have a full sheet which contains a lot of raw data. It will not however go beyond 500 x 500 cells so I use the range "A1:SF500" as a range named All.

I am trying to get the top ten values, numerical only, there are some text values (only "prog" and "autre") but I do not want them to count (as they will come back pretty often.

So my data is in a sheet called "Raw Data" and I want the top ten will be in an other sheet named "Stats Data" in the range "J42:J51".

you can filter by TopTen and count the entries with SOUS.TOTAL
Or look for the function GRANDE.VALEUR


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional