without using a middle step to sumif each criteria, is there a way i can find the 5 highest records.
right now i am using the large function to find the highest, 2nd highest, etc. but this doesnt work if a combination is higher.
for example
id value
1 100
2 125
3 90
4 200
5 150
6 120
3 90
the 5 largest would be 4,5,2,1,6...but if sum the 3 id, it's actually the 2nd largest value.
With your sample data in A1:B8
and this list in D1:D5
1
2
3
4
5
This regular formula begins the list of 5 highest totals:
E1: =LARGE(INDEX(SUMIF($A$2:$A$9,(FREQUENCY($A$2:$A$8,$A$2:$A$8)>0)*$A$2:$A$9,$B$2:$B$9),0),D1)
Copy that formula down through E5
Using your data, these are the results:
200
180
150
125
120
EDITED TO INCLUDE THIS SECTION:
This regular formula returns the ID's for the 5 highest sums...allowing for ties:
Copy that formula down through E5
Please Login or Register to view this content.
This are the returned values:
4
3
5
2
6
Is that something you can work with?
Last edited by Ron Coderre; 03-08-2013 at 05:34 PM.
Hey Ron, this actually works great, however it seems I hadn't explained my problem correctly.
Say the ids are not numbers and they are text.
id value
1c 100
2d 125
3b 90
4a 200
5a 150
6d 120
3b 90
seems like the frequency and sumif would need to be handled differently. thanks for your help!
Using your posted example...
and
D1:D5 contains
1
2
3
4
5
This regular formula begins the list of Col_A values with the largest sums
EDITED TO INCLUDE THIS VARIATION WHICH HAS TIE-BREAKERS:Please Login or Register to view this content.
Copy that formula down through D5Please Login or Register to view this content.
In that example, these are the results:
4a
3b
5a
2d
6d
Does that help?
Last edited by Ron Coderre; 03-12-2013 at 01:41 PM.
Yes, and how do I get the corresponding summed values?
This actually does not work for the data I am working with. Please see attached for my workbook.
Seems that it is literally just showing the first 5 IDs in the list (changes when you sort the values). it does not show the highest summed IDs.
I would also like to see the sum of the top values.
Well, I don't know how I posted the wrong version of the formula, but I surely did. I attached the edited workbook.
(I also included a pivot table approach that is really quite a bit easier to work with.)
Using your sample workbook, these are the correct formulas:
Top5 sheet
Copy those formulas down through Row_6.Formula:
Please Login or Register to view this content.
Better?
yes perfectly thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks