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!