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

#### fishfeller

##### New Member
 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.

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### fishfeller

##### New Member
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

##### MrExcel MVP
Code:
``````       ----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

##### New Member
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

##### New Member
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

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

#### fishfeller

##### New Member
It worked. Thanks!

You're welcome.

Replies
6
Views
352
Replies
1
Views
330
Replies
41
Views
668
Replies
1
Views
209
Replies
4
Views
483

1,196,010
Messages
6,012,840
Members
441,733
Latest member
MartijnB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back