Counting figures in a sorted list (Z to A) whose total reach 80

[kocakb]

Hello,


i have a table which shows distributors (140 dist) sales shares by months. i want to calculate number of distibutors whose total share reach 80%. but i couldn't write appropriate formula to do this. can you help me?


table like this


distributor 1 2 3 4 5 6

dist a 10 12

dist b 9 10

dist c 8 9

dist d 7 8

.

.

.

dist 135 4


# of dist. 20 15


from this sample table, 1.month, 20 distributors out of 135 made 80% of sales. how can i calculate this result.

 

[keymaster]

Ok, I tried to write an array formula that would do this, but cant seem to figure it out. So here is a formula using helper columns.


1. Create a similar table (distributors and monthly columns) in a separate sheet.

2. Assuming your original data is in A1:G135 (135 distributors for 6 months, first column has distributor name)

3. write a formula like =SUM(Sheet1!B$1:B1) and autofill it for the rest of range

4. Now, in your main sheet, against each month, you can use MATCH formula to find the closest match to 80% value like this,

=MATCH(SUM(B1:B135)*80%,Sheet2!B$1:B$135,1)+1


I would love to know if there is an array formula to reduce the helper columns. Otherwise, I think this is the basic technique you can use.