Excel formula required to speed up process of creating species rarefaction curves. Ecology!

[Tim_F]

Hi everyone,



I am currently completing my masters dissertation project in marine science.

Just as a bit of background: I have conducted experimental research where I have sampled fish species in the North Sea using trammel nets.

I have used 4 different protocols when sampling: net left in water for 24 hours and mesh size of 55mm, net left in water for 24 hours and mesh size of 120mm, net left in water for 4 hours and mesh size of 55mm and net left in water for 4 hours and mesh size of 120mm. This totals 4 different sampling protocols.

I conducted 4 replicates for each sampling protocol and I am comparing catch compositions between protocols.

I intend to produce rarefaction curves based on species richness for each protocol. This requires looking at cumulative species combinations of samples. E.g. Sample 1 which may have species (A,B,C,D) against Sample 2 which may have (B, D, E, F) - so the number of species found would be 6, as it is NEW species that are discovered.

The issue is that the example given above is simply just one of the many points I need to plot, and that is only for a scenario of two replicates. I also need to look at three replicates (combinations of 3 samples) and four replicates (combinations of 4 samples). I need to do every single combination.

I have attached my data spreadsheet with what I have so far so that hopefully this makes a bit more sense! The data presented is "species/absence" data - so if there is a 1 then it means the species was present in that catch, if there is a 0 then it was absent. Ignore the first two sheets. It is the hr and mm sheets that I am working with.

This is taking me absolutely ages and I'm sure there must be a way to present my species information and a formula I can use to speed up the process.

Any help would be extremely appreciated!

[gebobs]

I'm afraid I don't have any idea what you want. It does sound fascinating though so I am interested in helping out if I can. A few questions please and maybe some of this will get through my thick skull. Don't get your hopes up. :-)

In the 24/55 sheet, there is data in column A that the other three sheets do not have. Also, there is other data below the samples on this sheet and it only. Are these important or can I ignore them?

All 4 sheets have the same 31 species, but the last three have 7 additional species not present on the first sheet (24/55). Why is that? Seeing as only one species, a lesser weaver, was ever sampled and it only once, perhaps these may be disregarded?

The data appear to be a classic DOE design for 3 factors (soak, mesh, site) and 4 replicates.

[Tim_F]

I appreciate the time you've taken to help me out - managed to find a way around it so I no longer need to do it this way.

Thanks again though!