How do I calculate the number of species that make 90% of total abundance

[fishfeller]

	site 1	site2	site3
species 1	2		5
species 2		1
species 3			7
species 4	4
species 5		1	4
species 6
species 7	3	1	6
species 8
species 9	1		10
species 10		9

<tbody>
</tbody>

Above is an example of my data set. I want calculate how many species make up 90% of the total abundance at a given site. For example in site 1 above total abundance is 10, so 3 species make up 90% of the total abundance. I was wondering if there was a formula that would do this automatically since I need to do this for 220 sites.

 

[fishfeller]

I already have my data set converted to proportions if that is necessary. My data set is 220 sites by 70 species with the various species not occurring at every site.

 

[shg]

       ----A----- --B--- --C-- --D--
   1            1      4     9    10
   2            2      3     1     7
   3            3      2     1     6
   4            4      1     1     5
   5            5                  4
   6            6                   
   7            7                   
   8            8                   
   9            9                   
  10           10                   
  11           11                   
  12           12                   
  13        Total     10    12    32
  14      Species      3     3     5
  15                                
  16              site 1 site2 site3
  17   species 1       2           5
  18   species 2             1      
  19   species 3                   7
  20   species 4       4            
  21   species 5             1     4
  22   species 6                    
  23   species 7       3     1     6
  24   species 8                    
  25   species 9       1          10
  26   species 10            9

B2 and copy down and across: =IFERROR(LARGE(B$17:B$26, $A1), "")

B13 and copied across: =SUM(B1:B12)

B14 and copied across, and MIST be confirmed with Ctrl+Shift+Enter:

=MATCH(TRUE, SUBTOTAL(9, OFFSET(B1, 0, 0, ROW(INDIRECT("1:" & COUNT(B1:B12))))) >=90%*B13, 0)

 

[fishfeller]

I set up the matrix and everything worked except the final formula. I got a #N/A error. This was my formula

=MATCH(TRUE, SUBTOTAL(9, OFFSET(B6, 0, 0, ROW(INDIRECT("1:" & COUNT(B6:B72))))) >=90%*B73, 0)

Not sure how to fix that.

 

[fishfeller]

I set up the matrix and everything worked except the final formula. I got a #N/A error. This was my formula

=MATCH(TRUE, SUBTOTAL(9, OFFSET(B6, 0, 0, ROW(INDIRECT("1:" & COUNT(B6:B72))))) >=90%*B73, 0)

Not sure how to fix that.

 

[shg]

After you enter the formula in the formula bar, press and hold the Ctrl and Shift keys, then press Enter.

 

[fishfeller]

It worked. Thanks!

 

[shg]

You're welcome.