SUBTOTAL() Function to Summarize Data

I learned about this formula fairly recently as a better alternative to using the SUM() function when summarizing multiple totals in a single data column.

THE NEED:
Adding Subtotals or Totals to a table when there are multiple items.  This function is particularly useful because it can create a column grand total that ignores other subtotals.  I recently used this to calculate (1) the area of dense vegetation I was planning to plant, (2) the area of scattered (buffer) vegetation that was in my project site, and (3) total area being restored.  I have also used it to create grant budget tables.

Combine two columns of data into one

I'm going to start off this blog with a fairly obscure function. Not obscure because the formula is rare, but because I don't imagine that many people would be needing to do this. However, since I've come across the need to do this on more than one occasion, there may be other "-ologists" who wish to learn.

The Need:

I first used this formula when I had a large bird point count dataset with many plots in different management units (or habitats).  I wanted to combine the Unit Names with the Plot Numbers, which were in two separate columns, into a single column.

Having problems using Excel Macros?

If you're having trouble using macros in my macro library or other Excel spreadsheets, check the following about enabling macros and setting your Excel security settings.  Instructions are given for the 2003, 2007, and 2010 versions of Excel.

About Excel Macros

Macros in Excel are "little programs" that do things to data, retrieve data, generate data, or just about anything else in Excel.  You can automate just about anything in Excel using macros and assign that macro a shortcut key, which is useful if you have repetitive actions that you do frequently.  You'll find some useful macros for wildlife, ecology, and other monitoring or research data that I've already created and saved to my "Colin's Macro Library.xls" file.  Most of the macros in the worksheet have shortcut keys and are ready to run once the file is open on your computer, and the macros can be run in any Excel spreadsheet that's open on your computer as long as the macro library is open at the same time.

Below, I'll walk you through how to write your first macro.  The example will allow you to convert acres to hectares with the push of a shortcut key.

About Formulas

Many of the Excel tips used in this blog are formulas that calculate or do other functions to data in rows and columns that you specify.  There are many resources on the web where you can learn how formulas work, so this is a very basic primer:

1) Formulas begin with a "+" or "=" followed by some sort of action or calculation that you want to do to data in adjacent column(s) or row(s) that you specify.

Simple calculation formulas use "+", "-", "*", or "/" to add, subtract, multiply, or divide numbers by other numbers.

2) The "range," or cells of data that you want to perform calculations or other activities on are referred to by a letter (column that data is in) and number (row that data is in).

For instance, in the image below of an Excel worksheet, to calculate the sum of "Number" for the first two lines of data, a simple calculation would be "=G2+G3".  This would be entered in any blank cell, for instance in cell H2 or H3.

More complex formulas use functions to do actions on data that you specify in parenthesis.  For example the SUM() function adds all the numbers in the cells that you specify in the parenthesis (i.e., the "range").  For instance "=SUM(G2:G14)" in the example above would return a value of 17.  A logical location to put this formula would be in cell G15 below all the data.  Other simple functions are AVG(), used to average numbers, and COUNT() used to count non-blank cells in a range.  More complex functions use IF statements, VLOOKUP statements, and many, many others.

In this blog, I will often show how a formula is written in the column to the right of (or row below) where a formula exists in a worksheet.  For the example above, Column C is showing the results of a formula which combines text from column A and B.  The text in column D (labeled "Formula") are the exact text that is typed in Column C to produce that result.

More information can be found at these useful sites and others:
  http://office.microsoft.com/en-us/excel-help/examples-of-commonly-used-formulas-HP005200127.aspx
  http://spreadsheets.about.com/od/excelformulas/ss/formula_begin.htm
  http://www.ehow.com/how_2248061_use-formulas-excel.html
  http://excelformulas.info/

Some of my favorite Excel help forums are:
  http://www.mrexcel.com/
  http://www.excelforum.com/
  http://excelhelp.org/

Introduction

I'm intending to use this blog to share some tips and tricks I've learned along the way about managing natural resource research and monitoring data. I've slowly picked up some tricks about using Excel, ArcGIS, Google Earth, and other applications that have made my data management and analysis easier and more efficient. It's less about data analysis (consult a statistician for that) than how to efficiently enter, manipulate, and organize data in a useful way.  By no means am I an expert at any of these applications, but I've gained enough knowledge to use them as tools to do what I need them to. That means I get to spend more time in the field and less behind a desk, right? While this is not the typical "glamorous" part of working as an outdoor professional, I hope this information will be useful for those of us field biologists with "average" computer skills who have to occasionally deal with lots of lines of data.  The "tips and tricks" in this blog pertain specifically to wildlife, range, vegetation, botany, forestry, ecology, and other "-ology" data.  You can use the links, search, archive, and "topic" labels to the left to peruse this blog.  I started taking these notes for my own benefit of remembering new things that I learn, however if you find a use for this information, please enjoy and let me know!




Stuff in the works:

• A discussion forum where all of us can share, discuss, and learn new data management tips.
• Automatic converter of 4-letter AOU codes (bird species codes) into common or scientific names. (Done! Click here for bird species code converter and phylogenetic sort order tool.)
• Automatic assigning of bird family names to lists of species. (see link above)
• The same two (+) things for North American plants.
• Automatic re-ordering lists of species by phylogenetic order (useful for producing tables for reports or publication)
• USDA Plant species code converter.
• Nested "IF" statements.
• "Cash register" and inventory system for non-profit organization (NGO) fund raising use (kinda pushing the limits of a natural resource data-oriented blog).
• Other stuff:  If you have a request, let me know and I can try to provide a solution.  No guarantees, though (see disclaimer).