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.

Opening Excel 2007/2010 in New Windows for Multi-Monitor Support

If you don't use multiple monitors, you might not be annoyed by this change in post-2003 versions of Excel.  However, if you like to open Excel spreadsheets in separate windows, say, for sending them to different monitors, you've probably beat your head against your Biltmore stick wondering why the new versions of Excel don't allow this.  By separate windows, I'm not talking about being able to minimize multiple worksheets and view them with the "View Side by Side" feature.  I'm talking about opening totally different instances of Excel.

~~~~~~~~~~~~~~~~~~~~~~~~

Edit Dec 23, 2014: Recently I found another easy way to open multiple Excel windows in Office version 2010:  Simply shift-click on the Excel icon in the start menu or the task bar and another instance of Excel will open.  You can then navigate to a specific spreadsheet to open. You can't shift click on a spreadsheet file itself, so in some ways I find it easier to use the method below.

~~~~~~~~~~~~~~~~~~~~~~~~

The good news is that there are ways to accomplish this.  By far the easiest method that I've found so far is to add an Excel.exe shortcut to the "Send To" option of the context menu (it's an option that shows when you right-click on a file).  To do this, follow these instructions:

1. Open My Documents or My Computer
2. In the address box, type: %APPDATA%\Microsoft\Windows\SendTo
    Or you can navigate to something similar to:
    C:\Users\Colin\AppData\Roaming\Microsoft\Windows\SendTo
3. Open another instance of My Documents and navigate to your MS Office folder to locate EXCEL.EXE.  

    For me, it was in this folder:  C:\Program Files (x86)\Microsoft Office\Office12

4. Drag "EXCEL.EXE" into the SendTo folder.  This will create a shortcut to Excel in that folder
5. To open an Excel file in a separate window when there is already one open, right-click on the .xlsx or .xls file, hover over "Send To", and then select "Excel".

This unfortunately doesn't work for me if I'm opening a spreadsheet that's attached to an email since the "Send To" option isn't there, but it's really freed up my ability to manage my Excel windows over two monitors.

As a side note, I've also found a very useful free application that creates "Tabs" for quick switching and organizing of mutliple spreadsheets, Word documents, or PowerPoint presentations at the same time.  It's called OfficeTabs, found here (translated to English).

If you like this tip, click on the "+1" to show your support or leave a comment!

Copy & Fill Data Down Columns in Excel (Macro & non-Macro)

This is one of the features I've found useful many times over the years, so is one of the first tips that I'll introduce and will be the first macro that I'll cover on this blog.  To learn more about macros, click here.


THE NEED:


Do you ever have the same data to enter over and over and over and over again?  For instance, for a weather description column for Las Cruces (NM) in June:  Sunny, Sunny, Sunny, Sunny….

Or, how about multiple columns or a whole row of data that needs to be repeated for tens or hundreds of rows?  It's often useful and necessary that each datum (i.e. species and number detected) have all ancillary data associated with it (i.e. habitat type, plot #, date, soil type, etc).  This data format is necessary in order to use Pivot Tables, to export into SAS or other statistical software, etc.

For instance, in the example below each individual datum is a species and number detection and ancillary data are unit, habitat, plot #, and time.  To speed-up data entry, I often only enter data in rows when the value changes.  Here, I entered the unit name and habitat type only once for two avian point counts since they were the same.  I also only entered the plot number and time once for each plot. This isn't just lazy, but helps eliminate spelling errors that can cause data analysis problems later.  Now I want to copy all the unique data values and paste it so that it fills the blanks until the end of the dataset.

This entry explains how to easily copy data and fill blank cells below it in columns. I'll explain a way to do it without and with a macro.