Excel IF Statements (Shannon-Wiener Diversity Index Calculation Example)

'IF' statements are one of the most useful formula components to learn after you've learned some basic Excel formula writing.  A common, basic use of an IF statement is to say: "If the value of a cell is this, then do this action, if not then do something else." Or:

=IF(Condition You Want to Test, "Value" if True, "Value" if False)

In fact, those "Values" can be other conditional IF statements (making it a nested IF statement, which I'll cover later), or additional formulas.

To show how useful this function can be, we'll use the example of calculating Shannon-Wiener Diversity Indices.  The Shannon-Wiener Diversity Index, often symbolized by H' (or, H-prime), is a measure of species diversity that takes into consideration not only the number of species present, but includes their relative abundance in the population.  The example below shows 5 species of plants found along a line intercept range transect (this was a pre-monitoring survey before a prescribed fire).  The number associated with the species (in column 'C') shows the centimeters of the line covered by the plant.  You could also use number of individuals, biomass, or other measure of abundance.

With 5 species of plants, the species RICHNESS for this transect is 5.  To calculate Diversity, the formula is:
H' = - SUM (Pi * LN[Pi]), where Pi is the proportion that each species makes up of the total abundance found.  LN is the natural log of that same number.

The top portion of the image (yellow section) shows how one might calculate H' by calculating Pi in one column and then Pi*ln[Pi] in a second column, and then adding those values up (and taking the negative of that number).  However there are errors:

Click on picture to make it larger. Columns G through K show the formulas in A to E.

In the top portion, notice two things:

1. The $ (dollar sign) in column D (and J) makes an absolute reference to row 12 which shows total coverage (aka abundance).  I just point this out as an example.
2. Also notice the "#NUM!" error from E9 through E14.  That's because the natural log can't be calculated for zero.  And that's where the IF statement comes in.

The lower portion (green) introduces an IF statement in column E (and K) to eliminate the erroneous calculation.  For instance the value in D19 (as shown by the formula in K19) was calculated by stating:
"If the proportion (D19) is greater than zero, then calculate Pi*LN[Pi], if not then the value is nothing."
=IF(D19>0,D19*LN(D19),"")
And no errors.

To complete the diversity index cacluation, take the negative of the sum of all the values in column E, which comes to 0.945901.  This is a fairly low value, which shows that there's a low species richness and low "evenness" in representation among each species.

So, there you've seen the value of the IF statement in context of calculating the Shannon Index of Diversity.

As another side note, notice I used the "COUNTA" function rather than the "COUNT" function to calculate species richness because COUNTA counts non-empty cells, while "COUNT" counts cells with numbers in it and column B contains text, not numbers.