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

[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!

[katiespirations]

hi,

I wrote the code which help you to do that, using formulas is still painful job.
I used the code - see if that's what you want to get.

Kasia

[Tim_F]

Thank you so much this is exactly what I needed!

Much appreciated

[brunold85]

Can anyone help me with rarefaction curves? I have no idea how they are done. I need just some basic knowledge on how to do them and I can do them by myself. I would prefer excel.

[Gesabel10]

Hi I have just downloaded the file you posted several years ago - but can see no code....I guess it isn't there? Any advice for someone now trying to produce a rarefaction curve? Thanks

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread