Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Selecting the top 4 largest value cells??
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. |
#2
|
|||
|
|||
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 "pilaar39 - ExcelForums.com" wrote in message ... 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. |
#3
|
|||
|
|||
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. "pilaar39 - ExcelForums.com" wrote: 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. |
#4
|
|||
|
|||
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,SUMPRODU CT(--(B1:B10=2),A1:A10),0)+IF(COUNTIF(B1:B10,"<3")<4,SU MPRODUCT(--(B1:B10=3),A1:A10),0)+IF(COUNTIF(B1:B10,"<4")<4,SU MPRODUCT(--(B1:B10=4),A1:A10),0) Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=377739 |
#5
|
|||
|
|||
On Thu, 09 Jun 2005 08:07:25 -0700, lid
(pilaar39 - ExcelForums.com) wrote: 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. 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 |
#6
|
|||
|
|||
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 "pilaar39 - ExcelForums.com" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting cells using shortcut keys not mouse | Excel Discussion (Misc queries) | |||
selecting multiple cells with ctrl key | Charts and Charting in Excel | |||
Excel 97 - Selecting multiple cells from 2 different Pivot Tables | Excel Discussion (Misc queries) | |||
Selecting shaded cells in a row | Excel Worksheet Functions | |||
Ranking cells largest to smallest | Excel Discussion (Misc queries) |