Selecting the top 4 largest value cells??

  • Thread starter pilaar39 - ExcelForums.com
  • Start date
P

pilaar39 - ExcelForums.com

I am tryijng to sum the largest 4 values in a column of cells.

I used:
SUM(LARGE(A1:A10,{1,2,3,4}))
which seems to work fine in general,
however, if any of the top 4 largest values is the same, then it
yeilds an incorrect sum.

How can I overcome this failure? I need to include duplicate values in
my sum.
 
B

Bernie Deitrick

That formula works fine. How is the sum incorrect? You don't want to double
(or triple, or quadruple) count duplicates?

If you want the sum of the four largest unique values, how would you handle
the case where you have only three unique values?

HTH,
Bernie
MS Excel MVP
 
B

bj

try
=large(A1:A10,1)+if(is error(large(A1:A10,2),large(A1:A10,1),Large(A1:A10,2))+
if(is error(large(A1:A10,3),if(is
error(large(A1:A10,2),large(A1:A10,1),Large(A1:A10,2)),Large(A1:A10,3))+if(is
error(large(A1:A10,4),if(is error(large(A1:A10,3),if(is
error(large(A1:A10,2),large(A1:A10,1),Large(A1:A10,2)),Large(A1:A10,3)),large(A1:A10,4))))
I lost track of my parenthesis, so forgive me if you have to play with the
Parenthesis.
 
M

mangesh_yadav

Might not be the best way, but might just work.

You have your number in say A1:A10.
Enter their ranks in B1:B10 by using the formula:
=RANK(A1,$A$1:$A$10)
and copy down.

For the required total, use the formula:
=SUMPRODUCT(--(B1:B10=1),A1:A10)+IF(COUNTIF(B1:B10,1)<4,SUMPRODUCT(--(B1:B10=2),A1:A10),0)+IF(COUNTIF(B1:B10,"<3")<4,SUMPRODUCT(--(B1:B10=3),A1:A10),0)+IF(COUNTIF(B1:B10,"<4")<4,SUMPRODUCT(--(B1:B10=4),A1:A10),0)


Mangesh
 
R

Ron Rosenfeld

I am tryijng to sum the largest 4 values in a column of cells.

I used:
SUM(LARGE(A1:A10,{1,2,3,4}))
which seems to work fine in general,
however, if any of the top 4 largest values is the same, then it
yeilds an incorrect sum.

How can I overcome this failure? I need to include duplicate values in
my sum.
Click to expand...

Can you give examples of how you think it is working incorrectly?

Or is it that you want to count the 4 highest numbers but not double count
duplicates?

In that case, download and install the Laurent Longre's free morefunc.xll from
http://xcell05.free.fr/, and can use the following formula:

=SUM(LARGE(UNIQUEVALUES(A1:A10),{1,2,3,4}))

However, if there are fewer than four unique values in the range, you will get
an error message.


--ron
 
J

Jack Sprat

Actually, I think I made a mistake.. I 'thought' it was not working
correctly, but turns out it is.

What in fact I was doing, was setting up a sheet to track scores. But, in
this league, we only count the top 4 scores. So, if there were 8
participants, I wanted to sum up the total of the top 4, or the largest
values in a groups of cells. Example, if the cells had:
4,5,6,7,10,10,10,10, and I wanted the top 4 scores, I should have a result
of 40 in this case. Yes, I wanted to include duplicates.

At first I thought the function was not working, but now on closer
inspection, it appears to be working exactly as I want.

Sorry for the confusion and any unnecessary work I caused with this
question.

pl
 
Top