Large function
 

Hi Group
I am trying to find the top 10 values in a range. If a value is repeated
then it may show up say as the largest and next largest. How do I ignore
repeated values.

20
20
19
19
2
12
15
5
6
19

large(A1,A10,1)=20
large(A1,A10,2)=20
large(A1,A10,3)=19
large(A1,A10,4)=19
large(A1,A10,5)=19
etc
I would like to return
largest = 20
next largest =19
next largest =15
etc.

Thanks!

try something like this copied down fro the 2nd and 3rd, etc

=LARGE(myrng,SUMPRODUCT(COUNTIF(myrng,$a$1:a2))+1)

--
Don Guillett
SalesAid Software

"Sandy" wrote in message
...
Hi Group
I am trying to find the top 10 values in a range. If a value is repeated
then it may show up say as the largest and next largest. How do I ignore
repeated values.

20
20
19
19
2
12
15
5
6
19

large(A1,A10,1)=20
large(A1,A10,2)=20
large(A1,A10,3)=19
large(A1,A10,4)=19
large(A1,A10,5)=19
etc
I would like to return
largest = 20
next largest =19
next largest =15
etc.

Thanks!

or use max in f1 and this in f2. Array formula
=MAX(IF(myrng<F1,myrng))

--
Don Guillett
SalesAid Software

"Sandy" wrote in message
...
Hi Group
I am trying to find the top 10 values in a range. If a value is repeated
then it may show up say as the largest and next largest. How do I ignore
repeated values.

20
20
19
19
2
12
15
5
6
19

large(A1,A10,1)=20
large(A1,A10,2)=20
large(A1,A10,3)=19
large(A1,A10,4)=19
large(A1,A10,5)=19
etc
I would like to return
largest = 20
next largest =19
next largest =15
etc.

Thanks!

Another way would be to create a pivot table. Create a pivot field with your
data and make it an AVERAGE pivot field. Then go to the advanced field
settings screen of the field and choose Automatic Top / Bottom X for your
field. Ensure that the Using field field has your average pivot field name
in it.

Hope this offers an alternative.

Bill Horton

"Sandy" wrote:

Hi Group
I am trying to find the top 10 values in a range. If a value is repeated
then it may show up say as the largest and next largest. How do I ignore
repeated values.

20
20
19
19
2
12
15
5
6
19

large(A1,A10,1)=20
large(A1,A10,2)=20
large(A1,A10,3)=19
large(A1,A10,4)=19
large(A1,A10,5)=19
etc
I would like to return
largest = 20
next largest =19
next largest =15
etc.

Thanks!

In that my data spans numerous columns this looks to be the best solution. My
Range is Rows 9129:9492
my formula: In D9494 Copied down
LARGE($D$9129:$D$9492,SUMPRODUCT(COUNTIF($D$9129:$ D$9492,$D$9129:D9130))+1)

Im still getting some duplication of values
Thank you!

"Don Guillett" wrote:

try something like this copied down fro the 2nd and 3rd, etc

=LARGE(myrng,SUMPRODUCT(COUNTIF(myrng,$a$1:a2))+1)

--
Don Guillett
SalesAid Software

"Sandy" wrote in message
...
Hi Group
I am trying to find the top 10 values in a range. If a value is repeated
then it may show up say as the largest and next largest. How do I ignore
repeated values.

20
20
19
19
2
12
15
5
6
19

large(A1,A10,1)=20
large(A1,A10,2)=20
large(A1,A10,3)=19
large(A1,A10,4)=19
large(A1,A10,5)=19
etc
I would like to return
largest = 20
next largest =19
next largest =15
etc.

Thanks!