Top three performers with percentages

[Mel Smith]

I am trying to find a formula that will do the following on my spreadsheet:

List the top 3 performers with name and output and show their contribution as a percentage of the overall total in cells B70:D73 (name, contribution, percentage).

My data is arranged as follows: (1) Names in cells B4:B63 (2) Output in cells F4:F63 (3) Overall total in F64

Any ideas?

Mel

 

[hagia_sofia]

Please test this:

=LET(
performers,B4:B63,
outputs,F4:F63,
sum,F64,
top,LARGE(UNIQUE(outputs),SEQUENCE(3)),
seq,SEQUENCE(ROWS(outputs),,sum,0),
proportions,outputs/seq,
array,HSTACK(performers,outputs,proportions),
DROP(REDUCE("",top,LAMBDA(a,b,VSTACK(a,FILTER(array,outputs=b)))),1))

Please do note that if there are more performers with the same performance, there may be more than 3 results; if you are only interested in 3, what are the criteria to identify them when their performance is the same?

 

[Mel Smith]

Hi,

This is what I entered (all in one cell): =LET(performers, B4:B63,outputs,F4:F63,sum,F64,top,LARGE(UNIQUE(outputs),SEQUENCE(3)),seq,SEQUENCE(ROWS(outputs),,sum,0),proportions,outputs/seq,array,HSTACK(performers,outputs,proportions),DROP(REDUCE("",top,LAMDA(a,b,VSTACK(a,FILTER(array,outputs=b)))),1))

The error message showed: =NAME?

Mel

 

[myall_blues]

Another way. This will also show more than 3 if there is a tie.

Book1
	A	B	C	D
69
70	1	Person57	122	2.66
71	2	Person54	121	2.64
72	3	Person22	119	2.60
Sheet1

Cell Formulas
Range	Formula
B70:C72	B70	=FILTER(HSTACK($B$4:$B$63,$F$4:$F$63),$F$4:$F$63=LARGE($F$4:$F$63,$A70))
D70:D72	D70	=C70/$F$64*100
Dynamic array formulas.

 

[hagia_sofia]

Hi,

This is what I entered (all in one cell): =LET(performers, B4:B63,outputs,F4:F63,sum,F64,top,LARGE(UNIQUE(outputs),SEQUENCE(3)),seq,SEQUENCE(ROWS(outputs),,sum,0),proportions,outputs/seq,array,HSTACK(performers,outputs,proportions),DROP(REDUCE("",top,LAMDA(a,b,VSTACK(a,FILTER(array,outputs=b)))),1))

The error message showed: =NAME?

Mel

Thanks for the feedback. Hard to tell what causes the error - would this bring the same result:

=LET(
performers,B4:B63,
outputs,F4:F63,
total,F64,
top,LARGE(UNIQUE(outputs),SEQUENCE(3)),
seq,SEQUENCE(ROWS(outputs),,total,0),
proportions,outputs/seq,
array,HSTACK(performers,outputs,proportions),
DROP(REDUCE("",top,LAMBDA(a,b,VSTACK(a,FILTER(array,outputs=b)))),1))

If what myall_blues suggested works for you that would be great; if it does not could you please post sample of your data?

 

[Mel Smith]

Hi Murray,

Thank you for your helpful reply. With a small adjustment, it worked perfectly.

Thank you.

Mel

 

[Mel Smith]

Hello hagia-sofia,

As you can see from the above, Murray's solution gave me the result I was after. Thanks for your help.

Mel

 

[myall_blues]

Hi,


Thanks for the feedback. Hard to tell what causes the error - would this bring the same result:

=LET(
performers,B4:B63,
outputs,F4:F63,
total,F64,
top,LARGE(UNIQUE(outputs),SEQUENCE(3)),
seq,SEQUENCE(ROWS(outputs),,total,0),
proportions,outputs/seq,
array,HSTACK(performers,outputs,proportions),
DROP(REDUCE("",top,LAMBDA(a,b,VSTACK(a,FILTER(array,outputs=b)))),1))

If what myall_blues suggested works for you that would be great; if it does not could you please post sample of your data?

@hagia_sofia OP typed 'LAMDA' instead of LAMBDA.