How do I get the number of products that is 80% to of total in a Text box

[#1]

All:

I am trying to get the number of items where 80% of the cumulative total displayed in a text box.

An example of this is the following in a table format:

Rank	Product	Amount	Cumulative Amount
1	A	26	26
2	B	25	51
3	C	24	75
4	D	23	98
5	E	22	120
6	F	21	141
7	G	20	161
8	H	19	180
9	I	18	198
10	J	17	215
11	K	16	231
12	L	15	246
13	M	14	260
14	N	13	273
15	O	12	285
16	P	11	296
17	Q	10	306
18	R	9	315
19	S	8	323
20	T	7	330
21	U	6	336
22	V	5	341
23	W	4	345
24	X	3	348
25	Y	2	350
26	Z	1	351

There are 26 products in this instance.

80% of 351 is 280.8

I want to display the number of products not over the cumulative amount inside a text box.  In this case it would be 14.

Any help on this would greatly be appreciated.

[Anil_Babu_Samineni]

Aaron,

QVW?

[sunny_talwar]

May be this:

Count(DISTINCT {<Product = {"=RangeSum(Above(Sum(Amount), 0, RowNo()))/Sum(TOTAL Amount) <= 0.80"}>}Product)

[antoniotiman]

Sunny,

this works if Products are sorted by Amount.

I think You need Macro.

[sunny_talwar]

Very good point

[#6]

Yes my data is not necessarily in sorted order.

[sunny_talwar]

For anyone using QV12 or above, this should work:

=Count(DISTINCT {<Product = {"=Aggr(RangeSum(Above(TOTAL Sum(Amount), 0, RowNo(TOTAL))), (Amount, (NUMERIC, DESCENDING)), Product)/Sum(TOTAL Amount) <= 0.80"}>}Product)