Top 3 values from a list of limited by year

[michael248363]

I've been trying to figure out how to do this and searching here and the internet, but I can't figure it out. Maybe I'm making it harder than it needs to be and getting lost in the weeds. I'd like to do this without an array formula as I need speed with real-time calculations and every time I do array formulas it slows things down. Here is my problem recreated from a much larger, multi-sheet workbook

I can't figure out how to do the top 3 days and limit it to the year referenced in cell F1. What I get is in H2:I5. What I want is represented in H8:I11. Note that duplicate amount values cause the date to be listed as oldest first. Column C will always be sorted oldest at the top.

Book1
	C	D	E	F	G	H	I
1	Date	Amount		1/1/2020		The Results I Get
2	1/6/2020	$12.00				Top 3 Days	Daily Amount
3	3/30/2020	$37.00				7/8/2021	$90.00
4	5/15/2020	$67.00				5/15/2020	$84.00
5	5/15/2020	$84.00				5/15/2020	$84.00
6	9/8/2020	$84.00
7	11/11/2020	$50.00				The Results I Want
8	2/16/2021	$19.00				Top 3 Days	Daily Amount
9	6/4/2021	$55.00				5/15/2020	$84.00
10	7/8/2021	$90.00				9/8/2020	$84.00
11	9/1/2021	$23.00				5/15/2020	$67.00
12	11/1/2021	$84.00
Sheet1

Cell Formulas
Range	Formula
H3:H5	H3	=INDEX(DATE,MATCH(LARGE(AMOUNT,ROWS(H$3:H3)),AMOUNT,0))
I3:I5	I3	=INDEX(AMOUNT,MATCH(LARGE(AMOUNT,ROWS(I$3:I3)),AMOUNT,0))

Named Ranges
Name	Refers To	Cells
AMOUNT	=Sheet1!$D:$D	H3:I5
DATE	=Sheet1!$C:$C	H3:H5

 

[michael248363]

I don't know how to edit the topic after I submit it, but the topic should be "Top 3 values from a list limited by year"

 

[Radoslaw Poprawski]

This should be the solution 4 u

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	C	D	E	F	G	H	I
1	Date	Amount		01/01/2020		The Results I Get
2	06/01/2020	12				Top 3 Days	Daily Amount
3	30/03/2020	37				15/05/2020	84
4	15/05/2020	67				08/09/2020	84
5	15/05/2020	84				15/05/2020	67
6	08/09/2020	84
7	11/11/2020	50
8	16/02/2021	19
9	04/06/2021	55
10	08/07/2021	90
11	01/09/2021	23
12	01/11/2021	84
13
14
List

Cell Formulas
Range	Formula
H3:H5	H3	=INDEX($C$2:$C$100,AGGREGATE(15,6,(ROW($C$2:$C$100)-ROW($C$2)+1)/($D$2:$D$100=I3), COUNTIFS(I$3:I3,I3)))
I3:I5	I3	=AGGREGATE(14,6,$D$2:$D$100/(YEAR($C$2:$C$100)=YEAR($F$1)),ROWS(I$3:I3))

The reason your formulae are slow, is that you are using entire columns which is guaranteed to slow things down.

 

[alansidman]

Here is an alternative solution using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", Int64.Type}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Year", each ([Year] = 2020)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Amount", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Filtered Rows1" = Table.SelectRows(#"Added Index", each ([Index] = 1 or [Index] = 2 or [Index] = 3)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Year", "Index"})
in
    #"Removed Columns"

 

[Radoslaw Poprawski]

another PQ solution:

Book4
	A	B	C	D	E
1	Date	Amount		Choose your year:
2	01.06.2020	12		2021
3	30.03.2021	37
4	15.05.2020	67		Date	Amount
5	15.05.2020	84		07.08.2021	90
6	08.09.2021	84		08.09.2021	84
7	11.11.2020	50		11.01.2021	84
8	16.02.2021	19
9	06.04.2021	55
10	07.08.2021	90
11	09.01.2021	23
12	11.01.2021	84
13
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
D2	List	=V1#

let
    Source = Excel.CurrentWorkbook(){[Name="T_Year"]}[Content]{0}[#"Choose your year:"]
in
    Source

let
    Source = Excel.CurrentWorkbook(){[Name="T_Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= #date(T_Year, 1, 1) and [Date] <= #date(T_Year, 12, 31)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Amount", Order.Descending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",3)
in
    #"Kept First Rows"

also a PQ question.
I tried doing1st table more dynamic (if the header would change)
I added a step Table.columnNames, drilled it, and I was not able to reference that step marked red

let
    Source = Excel.CurrentWorkbook(){[Name="T_Year"]}[Content]{0}[COLOR=rgb(226, 80, 65)][#"Choose your year:"][/COLOR]
in
    Source

What ever I tried I failed since Mcode did not recognized that step,