count with the number of cells by that equal a target by summing the range.

[p4nny]

Hi,

In my example, I'm trying to create a formula that would return the "numbers of customers making up 80% of revenue".

I want to sum accounts cumulatively until the target is reached "80%") (or the closest match) and count how many accounts this is.

Many thanks for your help, Anthony

Column A Column B

TOTAL SALES	812,559.27
80% of TOTAL SALES	650,047.42
NUMBER OF ACCOUNTS MAKING 80% OF TOTAL SALES	????
Account	2014 Net Sales
P2063001	292,575.82
P2568001	110,493.34
P1790003	100,088.84
P1790004	94,744.40
P1126001	86,057.82
P2744002	53,009.23
P1790006	44,677.92
P0973000	18,881.50
P1790008	11,578.72
P2676002	610.08
P1790000	- 158.40

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Would you specify the expected outcome in the stead of the question marks?

 

[Comfy]

I think you need to specify one more criteria:

Minimum number of accounts making 80%......

or

Maximum number of acounts making 80%.......

As you could get multiple answers to your question.

 

[Comfy]

Although, now that I think about what it is that you are asking and why I guess this is to do with the Pareto principle.

In which case you could try this:

Pareto Chart in Excel - Easy Excel Tutorial

 

[p4nny]

Sorry, the expected outcome would be great.

 

[Aladin Akyurek]

Sorry, the expected outcome would be great.

You must tell us what that would be given your sample...

 

[p4nny]

Thanks for your reply.. I think minimum would be best.

Using the $650k as a target, cumulatively sum the values in each account until the $650k is achieved and return the number of accounts that make up the £650k.

 

[Comfy]

I think this will work, it will return the number at which the cumulative sum is closest to the 80% value.

Your data must be sorted in descending order.

Excel 2010
	A	B
1	TOTAL SALES	812,559.27
2	80% of TOTAL SALES	650,047.42
3	NUMBER OF ACCOUNTS MAKING 80% OF TOTAL SALES	5
4
5
6	Account	2014 Net Sales
7	P2063001	292,575.82
8	P2568001	110,493.34
9	P1790003	100,088.84
10	P1790004	94,744.40
11	P1126001	86,057.82
12	P2744002	53,009.23
13	P1790006	44,677.92
14	P0973000	18,881.50
15	P1790008	11,578.72
16	P2676002	610.08
17	P1790000	-158.4
Trade History

Cell Formulas
Range	Formula
B3	{=MATCH(MIN(ABS((MMULT(N(TRANSPOSE(ROW(B7:B17))<=ROW(B7:B17)),B7:B17)-B2))),ABS((MMULT(N(TRANSPOSE(ROW(B7:B17))<=ROW(B7:B17)),B7:B17)-B2)),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[p4nny]

WOW! Many thanks...

There appears to be one limitation though.. The data I sent is just a cut of the original. If I increase the range so from B17 to B100 (changing all of the B17 to B100) for example, the formula does not work. It works with the range up to B50.

Thanks again

 

[Comfy]

It worked for me:

Excel 2010
	A	B
1	TOTAL SALES	4,882,521.27
2	80% of TOTAL SALES	3,906,017.02
3	NUMBER OF ACCOUNTS MAKING 80% OF TOTAL SALES	50
4
5
6	Account	2014 Net Sales
7	P2063001	292,575.82
8	P2568001	110,493.34
9	P1790003	100,088.84
10	P1790003	97823
11	P2568001	97611
12	P2568001	97375
13	P2676002	96874
14	P2063001	96850
15	P1790004	94,744.40
16	P1790006	93921
17	P2676002	91398
18	P1790003	90219
19	P2568001	89574
20	P1790006	86227
21	P1126001	86,057.82
22	P2063001	82245
23	P1126001	81698
24	P1790004	81393
25	P1790008	78876
26	P2676002	77243
27	P2744002	77046
28	P1790000	76740
29	P1126001	76268
30	P1790006	75858
31	P2744002	75486
32	P2676002	75015
33	P1790003	74490
34	P0973000	73470
35	P2744002	73396
36	P1790003	69688
37	P1790003	67515
38	P2676002	67038
39	P1790000	65325
40	P1790008	64053
41	P2568001	63925
42	P1126001	63882
43	P2744002	62268
44	P2676002	59800
45	P1126001	59063
46	P1790004	57846
47	P2744002	56890
48	P2568001	56404
49	P2744002	56150
50	P1126001	53043
51	P2744002	53,009.23
52	P2063001	49688
53	P1790008	48221
54	P1790006	47321
55	P0973000	46714
56	P2063001	46483
57	P1126001	45410
58	P2568001	45003
59	P0973000	44902
60	P1790006	44,677.92
61	P0973000	44441
62	P2568001	43134
63	P2063001	38453
64	P2568001	38170
65	P1790008	36823
66	P1790004	36518
67	P0973000	30446
68	P1790006	29492
69	P2676002	28392
70	P1790004	27609
71	P2744002	27552
72	P1790000	26770
73	P1790008	25964
74	P1790004	24763
75	P1126001	24215
76	P1790006	22228
77	P1790003	20874
78	P1790004	20502
79	P0973000	19348
80	P0973000	18,881.50
81	P2744002	18656
82	P1790000	18542
83	P1790000	16438
84	P0973000	16386
85	P1790004	16155
86	P2063001	15857
87	P2063001	12688
88	P1790003	11800
89	P1790008	11,578.72
90	P1790000	11093
91	P1790003	10744
92	P1790000	10596
93	P1790004	9163
94	P1126001	7606
95	P1790008	7050
96	P2063001	4669
97	P1790008	2532
98	P2676002	610.08
99	P1790006	565
100	P1790000	-158.4
Trade History

Cell Formulas
Range	Formula
B1	=SUM(B7:B100)
B2	=B1*0.8
B3	{=MATCH(MIN(ABS((MMULT(N(TRANSPOSE(ROW(B7:B100))<=ROW(B7:B100)),B7:B100)-B2))),ABS((MMULT(N(TRANSPOSE(ROW(B7:B100))<=ROW(B7:B100)),B7:B100)-B2)),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.