Calculate top 70% based on total spend

[Caly]

I have a list of items with associated sales per line. Based on the line of sales and total amount I need to determine which lines represent the top 80%. The formula I’m using does not work when sorting. Need help please.

Formula Used here:	Formula Used here:
	$357,484.04​	=C5/$C$3	=IF(SUM($D$5:D5)<=80%,"Yes","No")
Item	Sales	% of Total	Top 80% - Yes or No?	this formula does not work though when sorting
Product 1	$17,729.16​	4.96%​	Yes	IF(SUM($D$5:D5)<=80%,"Yes","No")
Product 2	$17,670.50​	4.94%​	Yes
Product 3	$17,637.51​	4.93%​	Yes
Product 4	$17,501.01​	4.90%​	Yes
Product 5	$17,463.34​	4.89%​	Yes
Product 6	$17,433.79​	4.88%​	Yes
Product 7	$17,030.70​	4.76%​	Yes
Product 8	$17,016.03​	4.76%​	Yes
Product 9	$16,858.79​	4.72%​	Yes
Product 10	$16,729.06​	4.68%​	Yes
Product 11	$16,721.44​	4.68%​	Yes
Product 12	$16,483.20​	4.61%​	Yes
Product 13	$16,464.84​	4.61%​	Yes
Product 14	$16,078.88​	4.50%​	Yes
Product 15	$15,361.99​	4.30%​	Yes
Product 16	$15,277.02​	4.27%​	Yes
Product 17	$14,969.15​	4.19%​	Yes
Product 18	$14,882.70​	4.16%​	No
Product 19	$14,774.50​	4.13%​	No
Product 20	$14,607.41​	4.09%​	No
Product 21	$14,556.27​	4.07%​	No
Product 22	$14,236.75​	3.98%​	No

 

[dreid1011]

This doesn't produce exactly the result you're after (it's not mine, I managed to find it via Google) but it does identify the top results adding up to 80%:

Excel - how to select the largest values that account for 60% of data

I have 6 rows of data with a cost: I would like to select the top data entries whose cost account for e.g. 60% of my total cost. Here, the formula would flag Bananas, Lemons and Oranges since it i...

stackoverflow.com

Book2
	A	B	C	D	E	F	G	H
1
2		Formula Used here:	Formula Used here:
3			$357,484.04	=C5/$C$3	=IF(SUM($D$5:D5)<=80%,"Yes","No")
4		Item	Sales	% of Total	Top 80% - Yes or No?	this formula does not work though when sorting
5		Product 1	$17,729.16	4.96%	Yes	IF(SUM($D$5:D5)<=80%,"Yes","No")	Product 1	$17,729.16
6		Product 2	$17,670.50	4.94%	Yes		Product 2	$17,670.50
7		Product 3	$17,637.51	4.93%	Yes		Product 3	$17,637.51
8		Product 4	$17,501.01	4.90%	Yes		Product 4	$17,501.01
9		Product 5	$17,463.34	4.89%	Yes		Product 5	$17,463.34
10		Product 6	$17,433.79	4.88%	Yes		Product 6	$17,433.79
11		Product 7	$17,030.70	4.76%	Yes		Product 7	$17,030.70
12		Product 8	$17,016.03	4.76%	Yes		Product 8	$17,016.03
13		Product 9	$16,858.79	4.72%	Yes		Product 9	$16,858.79
14		Product 10	$16,729.06	4.68%	Yes		Product 10	$16,729.06
15		Product 11	$16,721.44	4.68%	Yes		Product 11	$16,721.44
16		Product 12	$16,483.20	4.61%	Yes		Product 12	$16,483.20
17		Product 13	$16,464.84	4.61%	Yes		Product 13	$16,464.84
18		Product 14	$16,078.88	4.50%	Yes		Product 14	$16,078.88
19		Product 15	$15,361.99	4.30%	Yes		Product 15	$15,361.99
20		Product 16	$15,277.02	4.27%	Yes		Product 16	$15,277.02
21		Product 17	$14,969.15	4.19%	Yes		Product 17	$14,969.15
22		Product 18	$14,882.70	4.16%	No
23		Product 19	$14,774.50	4.13%	No
24		Product 20	$14,607.41	4.09%	No
25		Product 21	$14,556.27	4.07%	No
26		Product 22	$14,236.75	3.98%	No
Sheet2

Cell Formulas
Range	Formula
G5:H21	G5	=INDEX(SORT($B$5:$C$26,2,-1),SEQUENCE(MATCH(TRUE,((MMULT(--(SEQUENCE(1,COUNT($C$5:$C$26))<=SEQUENCE(COUNT($C$5:$C$26))),SORT($C$5:$C$26,,-1))/$C$3)*100)>=79,0)),{1,2})
E5:E26	E5	=IF(SUM($D$5:D5)<=80%,"Yes","No")
Dynamic array formulas.

 

[StephenCrump]

Another way ...

Same results as @dreid1011 , plus a variation to include the first value that would make the sum >=80%.

	A	B	C	D	E	F	G	H	I
1
2		TOTAL	357,484			79.6%			83.7%
3
4		Item	Sales
5		Product 1	14,775		Product 3	17,729		Product 3	17,729
6		Product 2	16,483		Product 9	17,671		Product 9	17,671
7		Product 3	17,729		Product 17	17,638		Product 17	17,638
8		Product 4	16,465		Product 6	17,501		Product 6	17,501
9		Product 5	14,883		Product 21	17,463		Product 21	17,463
10		Product 6	17,501		Product 18	17,434		Product 18	17,434
11		Product 7	14,969		Product 8	17,031		Product 8	17,031
12		Product 8	17,031		Product 14	17,016		Product 14	17,016
13		Product 9	17,671		Product 22	16,859		Product 22	16,859
14		Product 10	14,607		Product 12	16,729		Product 12	16,729
15		Product 11	14,237		Product 20	16,721		Product 20	16,721
16		Product 12	16,729		Product 2	16,483		Product 2	16,483
17		Product 13	15,362		Product 4	16,465		Product 4	16,465
18		Product 14	17,016		Product 15	16,079		Product 15	16,079
19		Product 15	16,079		Product 13	15,362		Product 13	15,362
20		Product 16	15,277		Product 16	15,277		Product 16	15,277
21		Product 17	17,638		Product 7	14,969		Product 7	14,969
22		Product 18	17,434					Product 5	14,883
23		Product 19	14,556
24		Product 20	16,721
25		Product 21	17,463
26		Product 22	16,859
27
Sheet1

Cell Formulas
Range	Formula
C2	C2	=SUM(C5:C26)
F2,I2	F2	=SUM(F5:F22)/$C2
E5:F21	E5	=LET(s,SORT(B5:C26,2,-1),m,MATCH(80%*C2,SCAN(,INDEX(s,,2),LAMBDA(a,b,a+b))),TAKE(s,m))
H5:I22	H5	=LET(s,SORT(B5:C26,2,-1),m,MATCH(TRUE,80%*C2<=SCAN(,INDEX(s,,2),LAMBDA(a,b,a+b)),),TAKE(s,m))
Dynamic array formulas.