As I showed in my first Excel functions article, the serious meat in any spreadsheet is the formulas and functions that perform the calculations. Excel offers functions that you can use to make the process of computing the results simpler for many standard calculations. In this article I’ll show you some useful examples; I’ll tell you how to find functions to perform specific tasks and what to consider when building your own formulas.

Practical Formulas

Probably the first function you’ll have learned to use is =SUM, which calculates the sum of a range of numbers. This saves you from having to build simple additions one cell at a time. However this function will fail spectacularly if you have a filtered list.

The SUBTOTAL function is required when totaling only visible values in a filtered list. (Click for larger image).

If you filter a list to show only a small range of numbers and use =SUM to total the visible numbers –you’ll be disappointed as the function sums all values in the selected range – visible and not. Instead, when you total a filtered list you must use the SUBTOTAL function and, in fact, if you select the AutoSum button to calculate the sum automatically Excel will apply the SUBTOTAL function where the area to sum is a filtered list.

Once you know the limitations you can write your own SUBTOTAL function – it’s essentially the same as the SUM function but with an extra argument: =SUBTOTAL(9,A2:A10) – the number nine is the trigger that makes the SUBTOTAL function sum only the visible numbers in the filtered range A2:A10.

Working with Days

When planning projects and calculating timeframes, you often need to know the number of workdays between two dates. The NETWORKDAYS function can do this ‑‑ it calculates the number of days between two dates ignoring Saturdays, Sundays and any holidays that you specify. You’ll find the function in the Excel Analysis Toolpak. To check if the Toolpak is installed, choose Tools > Add-ins and then select the Analysis Toolpak checkbox.

Use the Analysis Toolpak’s NETWORKDAYS function to calculate the workdays between two dates. (Click for larger image).

To use the NETWORKDAYS function, enter the holiday days as a series of dates. Select the cells containing the dates and name them by choosing Insert > Name > Define; type in the holidays and press OK. To calculate the NETWORKDAYS, type the start and end dates in cells A1 and A2. The formula =NETWORKDAYS (A1,A2,holidays) will return the number of workdays between the two dates excluding weekends and the dates in the range you’ve named holidays.

Two other useful date functions are =NOW() and =TODAY(). The function =NOW inserts the current date and time into the cell and the =TODAY function enters the current date. You may need to format the cells to a date or time format using Format > Cell > Number Format for the date and time data to show correctly.

Referring to a Cell

To refer to the contents of one cell in another cell, type the cell reference with an = symbol. So, to refer to the contents of cell C2 in another cell, type =C2. If the value in cell C2 changes in the future, this cell’s value will change, too. You can also refer to a cell on another worksheet by including the sheet name – for example, to refer to the contents of C2 in Sheet 3 of the workbook use =Sheet3!C2.