Statistical
Shannon-Wiener Diversity Index (H'), Hill Number EXP(H') & Pielou's Evenness
Calculate ecological species diversity H' = -SUM(p_i * LN(p_i)), effective equal-abundance species EXP(H'), and Pielou's Evenness J' = H' / LN(S).
Combine species richness (S) and relative abundance evenness into H' and J'
Field ecologists and microbiome researchers quantify biodiversity with the natural-log Shannon-Wiener Index H' = -SUM(p_i * LN(p_i)), where p_i = Count_i / TotalCount is each species' share of the community (guarding zero-count absent species via IF(B2 = 0, 0, ...) because LN(0) throws #NUM!).
Exponentiating H' gives MacArthur's Effective Number of Species N1 = EXP(H') (Hill Number q = 1), while dividing H' by theoretical max diversity LN(S) (where S = COUNTIF(B2:B5, ">0") present species) gives Pielou's Evenness J' on a clean 0 to 1 scale!
Use nonnegative numeric counts from a consistent sampling effort. An empty community has no defined relative-abundance distribution; Pielou evenness is undefined when fewer than two species are present. These sample summaries are not estimates corrected for unseen species or unequal sampling effort.
| Ecological Biodiversity Metric (Counts: 50, 25, 15, 10; N=100, S=4) | Formula | Result |
|---|---|---|
| Shannon-Wiener Diversity (H') | -SUM(p_i * LN(p_i)) | 1.2080 nats |
| Effective Equal Species N1 = EXP(H') | EXP(1.2080) | 3.35 effective species |
| Pielou's Evenness J' (0 to 1) | H' / LN(4) = 1.2080 / 1.3863 | 0.8714 (87.1% even) |
| Perfectly Even Community (25, 25, 25, 25) | H' = LN(4) = 1.3863 | J' = 1.0000 (N1 = 4.00) |
Per-species -p_i * LN(p_i) term (zero-safe) and total H'
C2:C5 computes =IF(B2 = 0, 0, -(B2 / SUM($B$2:$B$5)) * LN(B2 / SUM($B$2:$B$5))) so absent species contribute 0 instead of #NUM!.
=IF(OR(COUNT(B2:B5)<>4,MIN(B2:B5)<0),"Invalid inputs",IF(SUM(B2:B5)=0,"No observations",ROUND(SUM(C2:C5), 4)))Pielou's Evenness J' = H' / LN(Richness S)
Normalizes H' onto [0, 1] using present species richness COUNTIF(B2:B5, ">0").
=IF(OR(COUNT(B2:B5)<>4,MIN(B2:B5)<0),"Invalid inputs",IF(COUNTIF(B2:B5,">0")<2,"Undefined evenness",ROUND(SUM(C2:C5) / LN(COUNTIF(B2:B5, ">0")), 4)))Try the example
Use the original values or change them to test your understanding.