Help with my Dynamic Top 10 Formula

[DanielJeff]

Morning

So i'm after a bit of help with a formula i've written =SORT(FILTER(Totals,Totals[Sales]>=LARGE(Totals[Sales],G2)),4,-1).

I'm trying to create a 10 list that changes each time a sales have updated. The above formula works and gives me what I'm after. The formula reads a cell that i can adjust to view the amount users shown. so if i want to see the top 2 i can change a cell to 2, 3 etc. I want it to always display the top 10. But the formula ends up showing everyone when i set it to 10. This is because currently only 7 people have sold stuff, the formula then displays all the people with 0 sales.

I need it so that i can leave the number to display the top 10, but then for the results to display only people who have sold something and not everyone else. SO essentially leaving numbers 8-10 blank until people record a sale


Apologise if its not the best description. I've attached my sheet so you can see where I'm up to.

Thanks
Daniel

 

[DanielJeff]

Just in case the image is to small to see.

Name	Code	Department	Sales	Total Royalty		Top Sales
Kiana Collins	FP 1	Agrii	1.000​	£187.00​		10​
Miles Frederick	FP2	Agrii	0.000​	£0.00​
Jaycee Best	LS 1	Agrii	0.000​	£0.00​
Adeline Harrington	LS 2	Agrii	0.000​	£0.00​
Mary James	LS 3	Agrii	3.000​	£1,500.00​
Carolina Todd	LS 4	Agrii	0.000​	£0.00​
Alexa Foster	LS 5	Agrii	0.000​	£0.00​
Kayden Hopkins	LS 6	Agrii	10.000​	£0.00​
Gabriela Fleming	SB 1	Agrii	0.000​	£0.00​
Andrea Larson	SB 2	Agrii	2.000​	£230.00​
Rafael Thomas	SB 3	Agrii	0.000​	£0.00​
Elizabeth Miller	SB 4	Agrii	0.000​	£0.00​
Russell Ward	SB 5	Agrii	5.000​	£0.00​
Ariana Beil	SB 6	Agrii	0.000​	£0.00​
Roselyn Turner	SB 7	Agrii	6.000​	£0.00​
Roger Bishop	SB 8	Agrii	0.000​	£0.00​
Sydney Galvan	SB 9	Agrii	0.000​	£0.00​
Kingsley Bruce	SB 10	Agrii	0.000​	£0.00​
Teresa Maynard	SB 11	Agrii	0.000​	£0.00​
Landry Stokes	SB 12	Agrii	0.000​	£0.00​
Miranda Reilly	SB 13	Agrii	4.000​	£0.00​
Brooklynn Morgan	SB 14	Agrii	0.000​	£0.00​
Harvey Montes	SB 15	Agrii	0.000​	£0.00​
=SORT(FILTER(Totals,Totals[Sales]>=LARGE(Totals[Sales],G2)),4,-1)	1​	Kayden Hopkins	LS 6	Agrii	10​	0​
	2​	Roselyn Turner	SB 7	Agrii	6​	0​
	3​	Russell Ward	SB 5	Agrii	5​	0​
	4​	Miranda Reilly	SB 13	Agrii	4​	0​
	5​	Mary James	LS 3	Agrii	3​	1500​
	6​	Andrea Larson	SB 2	Agrii	2​	230​
	7​	Kiana Collins	FP 1	Agrii	1​	187​
	8​	Miles Frederick	FP2	Agrii	0​	0​
	9​	Jaycee Best	LS 1	Agrii	0​	0​
	10​	Adeline Harrington	LS 2	Agrii	0​	0​
		Carolina Todd	LS 4	Agrii	0​	0​
		Alexa Foster	LS 5	Agrii	0​	0​
		Gabriela Fleming	SB 1	Agrii	0​	0​
		Rafael Thomas	SB 3	Agrii	0​	0​
		Elizabeth Miller	SB 4	Agrii	0​	0​
		Ariana Beil	SB 6	Agrii	0​	0​
		Roger Bishop	SB 8	Agrii	0​	0​
		Sydney Galvan	SB 9	Agrii	0​	0​
		Kingsley Bruce	SB 10	Agrii	0​	0​
		Teresa Maynard	SB 11	Agrii	0​	0​
		Landry Stokes	SB 12	Agrii	0​	0​
		Brooklynn Morgan	SB 14	Agrii	0​	0​
		Harvey Montes	SB 15	Agrii	0​	0​

 

[Fluff]

How about

=TAKE(SORT(FILTER(Totals,Totals[Sales]>0),4,-1),G2)

 

[DanielJeff]

How about

=TAKE(SORT(FILTER(Totals,Totals[Sales]>0),4,-1),G2)

Worked brilliantly and and has fixed the problem i was having.

Thanks for your help

 

[Fluff]

You're welcome & thanks for the feedback.

 

[DanielJeff]

You're welcome & thanks for the feedback.

Fluff

Could i pick your brain about about another formula issue I'm having. Because my knowledge of more complex formulas is pretty beginner, I tend to look at a bunch of website and piece together bits of what i think will roughly work. Its gets me about 90% of the way, but i often end up with long complicated formulas.
Your formula was a lot cleaner and simpler than the one I had and did exactly what i needed it to.
Would you be able to look at the below and see if there is anything you can recommend. Its pretty much the same as my last question about a top 10 that doesn't include the 0's. But I also need it to read departments. below is an example of what i mean.

Apologies again if its not been explained to good. I've included the formulas i've used for each column in my top 10 under the table

Name	Code	Department	Sales	Total Royalty
Kiana Collins	FP	Agrii	1.000​	£187.00​
Miles Frederick	FP	Warehouse	2.000​	£0.00​
Jaycee Best	FP	Front Office	3.000​	£0.00​
Adeline Harrington	FP	Remote	0.000​	£0.00​
Mary James	FP	Machine	0.000​	£1,500.00​
Carolina Todd	LS	Agrii	1.000​	£0.00​
Alexa Foster	LS	Warehouse	2.000​	£0.00​
Kayden Hopkins	LS	Front Office	3.000​	£0.00​
Gabriela Fleming	LS	Remote	0.000​	£0.00​
Andrea Larson	LS	Machine	0.000​	£230.00​
Rafael Thomas	SB	Agrii	0.000​	£0.00​
Elizabeth Miller	SB	Warehouse	1.000​	£0.00​
Russell Ward	SB	Front Office	2.000​	£0.00​
Ariana Beil	SB	Remote	3.000​	£0.00​
Roselyn Turner	SB	Machine	0.000​	£0.00​
Roger Bishop	SW	Agrii	1.000​	£0.00​
Sydney Galvan	SW	Warehouse	2.000​	£0.00​
Kingsley Bruce	SW	Front Office	3.000​	£0.00​
Teresa Maynard	SW	Remote	0.000​	£0.00​
Landry Stokes	SW	Machine	0.000​	£0.00​
Miranda Reilly	WW	Agrii	1.000​	£0.00​
Brooklynn Morgan	WW	Warehouse	2.000​	£0.00​
Harvey Montes	WW	Front Office	0.000​	£0.00​
	=INDEX($A$2:$A$24,SMALL(IF(($D$2:$D$24=D27)*($B$2:$B$24=$A$31),ROW($D$2:$D$24)-ROW($D$1)),COUNTIF(D27:$D$27,D27)))	=FILTER(Totals[Department],Totals[Name]=B27,"")	=LARGE(IF($B$2:$B$24=$A$31,$D$2:$D$24),ROW(B27)-ROW(B$26))
Top Sales	Jaycee Best	Front Office	3​
10	Miles Frederick	Warehouse	2​
	Kiana Collins	Agrii	1​
Department	Adeline Harrington	Remote	0​
FP	Mary James	Machine	0​

 

[Fluff]

As this is a different question, it needs a new thread. Thanks

 

[DanielJeff]

no problem, i've just added it as a new thread