Need help with calculations in Excel
Solved
MA
-
Honoré -
Honoré -
Hello,
I’m turning to you because I’m not sure how to proceed! I’d like to calculate, for example, the Shannon diversity index for 90 stations. The problem is that in my Excel matrix I have a number of rows equal to the number of species in each station, so this number of rows is variable. Also, I have x rows and I have only one result for the diversity index, so how should I proceed? Should I rearrange my Excel matrix? because I would like later to be able to drag the formula for the 90 stations and not do everything by hand (on the computer!). Moreover, I must not merge cells because, since I then have to export this Excel matrix to the R software, I don’t think it accepts merged cells. I’m sending you an example of my data.
Thank you very much for your help.
Best regards,
MA
Species Point, Effectif pi, pilog2pi, pi², Shannon
2490 BUTBUT 1 0.033333333 -0.16356302 0.001111111
2490 CARCHL 1 0.033333333 -0.16356302 0.001111111
2490 CORONE 4 0.133333333 -0.387585413 0.017777778
2490 EMBCIT 3 0.1 -0.332192809 0.01
2490 FRICOE 1 0.033333333 -0.16356302 0.001111111
2490 HIPICT 1 0.033333333 -0.16356302 0.001111111
2490 HIPPOL 1 0.033333333 -0.16356302 0.001111111
2490 PARMAJ 3 0.1 -0.332192809 0.01
2490 PHYCOL 1 0.033333333 -0.16356302 0.001111111
2490 PRUMOD 1 0.033333333 -0.16356302 0.001111111
2490 STUVUL 6 0.2 -0.464385619 0.04
2490 SYLCOM 2 0.066666667 -0.260459373 0.004444444
2490 TROTRO 1 0.033333333 -0.16356302 0.001111111
2490 TURMER 3 0.1 -0.332192809 0.01
2490 TURPHI 1 0.033333333 -0.16356302 0.001111111
I’m turning to you because I’m not sure how to proceed! I’d like to calculate, for example, the Shannon diversity index for 90 stations. The problem is that in my Excel matrix I have a number of rows equal to the number of species in each station, so this number of rows is variable. Also, I have x rows and I have only one result for the diversity index, so how should I proceed? Should I rearrange my Excel matrix? because I would like later to be able to drag the formula for the 90 stations and not do everything by hand (on the computer!). Moreover, I must not merge cells because, since I then have to export this Excel matrix to the R software, I don’t think it accepts merged cells. I’m sending you an example of my data.
Thank you very much for your help.
Best regards,
MA
Species Point, Effectif pi, pilog2pi, pi², Shannon
2490 BUTBUT 1 0.033333333 -0.16356302 0.001111111
2490 CARCHL 1 0.033333333 -0.16356302 0.001111111
2490 CORONE 4 0.133333333 -0.387585413 0.017777778
2490 EMBCIT 3 0.1 -0.332192809 0.01
2490 FRICOE 1 0.033333333 -0.16356302 0.001111111
2490 HIPICT 1 0.033333333 -0.16356302 0.001111111
2490 HIPPOL 1 0.033333333 -0.16356302 0.001111111
2490 PARMAJ 3 0.1 -0.332192809 0.01
2490 PHYCOL 1 0.033333333 -0.16356302 0.001111111
2490 PRUMOD 1 0.033333333 -0.16356302 0.001111111
2490 STUVUL 6 0.2 -0.464385619 0.04
2490 SYLCOM 2 0.066666667 -0.260459373 0.004444444
2490 TROTRO 1 0.033333333 -0.16356302 0.001111111
2490 TURMER 3 0.1 -0.332192809 0.01
2490 TURPHI 1 0.033333333 -0.16356302 0.001111111
6 answers
For your information:
The Shannon index is a metric used to measure biodiversity.
H' = - ? pi ln pi
H' : Shannon biodiversity index
i : a species in the study environment
pi : Proportion of species i relative to the total number of species (S) in the study environment (or species richness of the environment), which is calculated as follows:
p(i) = ni / N
where ni is the number of individuals for species i and N is the total count (the individuals of all species).
It is possible to arbitrarily choose the logarithm base; therefore, you often see in scientific literature log or log base 2 instead of ln.
This index quantifies the heterogeneity of biodiversity in a study environment2 and thus allows observing a change over time. This index always ranges from 0 to ln S (or log S or log2 S, depending on the chosen logarithm base)
This index is one of the most well-known and widely used by specialists.
The Shannon index is a metric used to measure biodiversity.
H' = - ? pi ln pi
H' : Shannon biodiversity index
i : a species in the study environment
pi : Proportion of species i relative to the total number of species (S) in the study environment (or species richness of the environment), which is calculated as follows:
p(i) = ni / N
where ni is the number of individuals for species i and N is the total count (the individuals of all species).
It is possible to arbitrarily choose the logarithm base; therefore, you often see in scientific literature log or log base 2 instead of ln.
This index quantifies the heterogeneity of biodiversity in a study environment2 and thus allows observing a change over time. This index always ranges from 0 to ln S (or log S or log2 S, depending on the chosen logarithm base)
This index is one of the most well-known and widely used by specialists.