Why Do You Need Formulas and Functions?

This How-To Geek School class is intended for people who use Excel, or try to use Excel but are intimidated by the concept of formulas and functions. Its objective is to make you an Excel pro or at least, a competent amateur, in just a few lessons.

Note: for the uninitiated, this is the very first lesson in the first series for How-To Geek School. We will be running a new lesson every single weekday, Monday through Friday, from now until we’ve covered every subject. So stay tuned for more, and we’ll be making a formal announcement about how it works next week.

What is Excel and Why Do We Need It?

If you are an office administrator, you probably use Excel. Ditto, if you are a salesman. If you are a financial analyst, of course, you need Excel. For these and many other professions, working with spreadsheets is an essential tool in the daily routine.

People who are math wizards will probably immediately take to Excel and use it like a natural extension of their computational processes. Most of the rest of us … probably need some help in that department.

If you did not pay attention in high school algebra class, don’t fret! All you need to do is learn some basic knowledge of formulas and functions from this How-To Geek School series and apply them to your work.

If you don’t know whether or not you need Microsoft Excel, then you probably have not been shown what it can do. Once you do know, then you can find ways to use it in your work to make complicated and tedious tasks easier.

Microsoft Excel is more than just a program to enter names and numbers. For example, if you simply want to list the names of students in your class then you can just make a Word document. Word is fine for writing essays and making lists but to do calculations, you need Excel’s formulas and functions.

A Brief Tour of the Excel Interface

Before we dive in, let’s just briefly review the Excel interface. You may already know all this stuff, but it never hurts to review.

When you open a blank Excel spreadsheet, it is known as a “workbook” or “book” and each spreadsheet in a book is a known as a “worksheet” or “sheet”.

The top of the app is dominated by the “ribbon” (grey), which sub-divided into “tabs” (red), which are then further sub-divided into “sections” (blue).

Below the ribbon is the “name box”, which lets you rename “cells” and then to the right of that is an area that allows you to create your all-important “formulas”. If you hover over each icon, you can see the purpose of each button, but what we’re most concerned with is the wide area where our formulas and functions will be displayed.

Finally, the worksheet is arranged in “columns” (denoted by letters) and “rows” (indicated by numbers). Each location is a “cell” and a group of cells is known as a “range”. If you have multiple sheets in a book, you can quickly flip through them using the tabs seen below the sheet. Below that, in the grey status bar, you can adjust your page views and zoom level.

That’s it, pretty simple, and just a short overview of some of the terminology you’ll encounter in this HTG School series.

What is a Function?

A function is a calculation or operation that returns a result. The inputs in a function are called “arguments”.

All functions begin with an equals sign [=]. That way Excel knows not to treat the arguments as text. For example, =AVERAGE(2,4) is a function but AVERAGE(2,4) is just a string of text. Without an equals sign, Excel will not calculate a result. The arguments in this function are 2 and 4.

Note, Excel uses upper-case letters to list functions, but you can use lower or upper-case letters when you write them.

In Excel, the “Function Library” can be found on the “Formulas” tab.

There are 13 categories of functions, some of which are:

• Mathematical: AVERAGE() – calculates the average of a series of numbers.
• Date and time: DATEVALUE() – converts a string of text like “30 November 2013” to a number so that you can use this number in other date and time functions. You cannot do math with dates unless you convert them to numbers first. We explain this in detail in Lesson 4.
• Text: LEN() – returns the length of a string. For example =LEN(“Excel”) is 5.
• Logical: IF() – the IF() function is written like =IF(<test>, then A, else B). So, if “test” is true, then the result is A; if “test” is not true, then B.
• Lookup and Reference: These are needed to lookup values elsewhere in the spreadsheet. For example, VLOOKUP looks in a table of values to find one cell.

How could you use this last one? Well, to get the day of the week in text from a date function. You can use VLOOKUP to scan a table to turn this number into something easier to understand, like “Wednesday”.

There are also special functions for financial, engineering, and statistics which are listed separately on the “More Functions” menu.

What is a Formula?

A formula is combination of “operators”, “operands”, and “functions”.

For example, the function =SUM adds a list of numbers (it is so commonly used, that is listed on the first menu in Excel, abbreviated by the Greek letter Sigma (Σ), which is the notation that mathematicians use to sum a series).

You use a formula like doing a calculation by hand. For example, you could put your family budget into a formula like this:

Remaining cash = (4 * weekly salary) – mortgage – food – utilities

The operators are multiply [*] and subtract [-]. The operands are the values “weekly salary”, “mortgage”, “food”, and “utilities”. The result is “remaining cash”.

Names and Addresses

The values for “food” and the other operands are names that you define in Excel. Without a “name”, you would have to use the “address”.

The address of a cell is written using row-column notation. The rows are given numbers and the columns, letters. The first cell in the spreadsheet is A1. When you have reached the end of the alphabet, the rows are numbered AA, AB, BA, BB, and so forth.

Formulas can be more complicated than the family-budget example. In high school you learned that that the area of a circle is the radius times pi squared or πr2.

In Excel, you can write this using the formula =PI() * radius ^ 2.

Here, PI() is the function that returns the number 3.14 and “radius” is a “name” we have given to a cell that contains the radius; the “operators” are the exponent (^) and multiplier (*).

Order and Precedence

Parentheses are used to indicate the order and precedence in calculations.

The area of a circle can also we written πrr but not (πr)2, so you need to understand order and precedence to get right answer. Exponents are evaluated before multiplication, so parentheses are not needed in this case. The function squares the radius first then multiplies that by Pi.

If you wanted to eliminate any possible doubt you could be explicit and write =Pi * (radius ^2).

We will explore order and precedence further in Lesson 2.

Coming up Next …

For the remainder of this series, we will concentrate on a four key areas:

Lesson 2: Defining and Creating Formulas

To get started, the first item to explore is how to create formulas and understand the basic rules:

• How to create a formula
• Calculation operators
• Order and precedence
• Demonstrate how a function is used within a formula

Lesson 3: Cell Reference and Formatting

In this lesson we discuss the three types of cell reference (relative, absolute, and mixed), formatting, how to move or copy and formula, and circular references.

Suppose you have a formula in cell B1 that says =A1*2. If you copy that formula to cell B2 (one cell to the right), then the cell reference changes too.

The new formula says B1*2. You can use the dollar sign ($) to anchor the cell reference so that copying it does not change where it points (don’t worry, we cover this in a bit).

Lesson 4: Useful Functions You Should Get to Know

This lesson explains some of most commonly-used functions and how we can apply them in useful calculations and how to “nest” functions.

To nest a function means put one function inside another. For example, if you want to know what day today is, write =WEEKDAY(NOW()).

NOW() gives today’s date and WEEKDAY() turns it into a number from 1 to 7. NOW() is “nested” inside WEEKDAY().

Lesson 5: Advanced Functions: If, Nested-If, and Lookups

If you want to create complex models or algorithms, you will need to know how to combine logic and lookup functions including IF and nested IF statements and lookups including VLOOKUP and HLOOKUP.

An example of lookup function would be =COLUMN(). This gives the column number of a cell reference, e.g., A=COLUMN(A1).

We’ll also briefly cover pivot tables. A pivot table is used to extract meaning from data. For example you could take a list of sales and group them by region or sales person to give you the totals for each.