LinkBack URL
About LinkBacks
Hello, I wonder if anyone could help me.
I have columns A,B,C with the date, name and a value respectively. I am trying to extract the data between 2 dates and then list the top 5 highest to lowest in column D. Can anyone help ? Thanks
Hello, I wonder if anyone could help me.
I have columns A,B,C with the date, name and a value respectively. I am trying to extract the data between 2 dates and then list the top 5 highest to lowest in column D. Can anyone help ? Thanks
Post a SMALL sample file (no more than 20 rows worth of data) and show us what results you expect.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Date Name Percentage
16 04 16 Peter 66%
13 04 16 Bob 80%
21 04 16 Andre 99%
17 04 16 Lucy 12%
27 04 16 Gordon 36%
15 04 16 Terry 14%
17 04 16 Lisa 23%
26 05 16 Sally 75%
23 04 16 Maggie 26%
23 04 16 Allen 95%
18 04 16 Linda 57%
Date: 13 04/16- 22/04/16
RESULTS:
TOP 5
Andre 99%
Bob 80%
Peter 66%
Linda 57%
Lisa 23%
With dates in E1 and F1 and data in A:C
in E2
=IF(($A$2:$A$12>=$E$1)*($A$2:$A$12<=$F$1),(LARGE(($A$2:$A$12>=$E$1)*($A$2:$A$12<=$F$1)*($C$2:$C$12),ROWS($1:1))))
Enter with Ctrl+Shift+Enter and copy down
In F2
=INDEX($B$2:$B$12,MATCH($E2,$C$2:$C$12,0)) copy down
Try this...
Data Range
Enter this array formula** in H1 and copy down to H5:
=LARGE(IF(A$1:A$11>=E$1,IF(A$1:A$11<=F$1,C$1:C$11)),ROWS(H$1:H1))
Format as %
Enter this array formula** in G1 and copy down to G5:
=INDEX(B:B,SMALL(IF(A$1:A$11>=E$1,IF(A$1:A$11<=F$1,IF(C$1:C$11=H1,ROW(B$1:B$11)))),COUNTIF(H$1:H1,H1)))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
I made the assumption that there will always be at least 5 data points that fall within the date range.
Exactly what I wanted, thank you very much.
You're welcome. Thanks for the feedback!
Building on top of this formula, how can I ignore any figures that say 100%.
Date Name Percentage
16 04 16 Peter 66%
13 04 16 Bob 100%
21 04 16 Andre 100%
17 04 16 Lucy 12%
27 04 16 Gordon 36%
15 04 16 Terry 14%
17 04 16 Lisa 23%
26 05 16 Sally 75%
23 04 16 Maggie 26%
23 04 16 Allen 95%
18 04 16 Linda 57%
14 04 16 Greg 21%
15 04 16 Lulu 20%
Results
TOP 5
Peter 66%
Linda 57%
Lisa 23%
Greg 21%
Lulu 20%
Try this...
Data Range
This array formula** entered in H1:
=LARGE(IF(A$1:A$13>=E$1,IF(A$1:A$13<=F$1,IF(C$1:C$13<1,C$1:C$13))),ROWS(H$1:H1))
Format as Percentage
This array formula** entered in G1:
=INDEX(B:B,SMALL(IF(A$1:A$13>=E$1,IF(A$1:A$13<=F$1,IF(C$1:C$13<1,IF(C$1:C$13=H1,ROW(B$1:B$13))))),COUNTIF(H$1:H1,H1)))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Select G1:H1 and copy down to G5:H5
thanks so much!
You're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks