Spreadsheet Functions - Google

Spreadsheet Functions

Class Description This is an introduction to the use of functions in spreadsheets, with a focus on Microsoft Excel and Google Drive Spreadsheets. The main topics are arithmetic calculations and order of precedence; working with dates and times; some of the most commonly used functions (SUM, AVERAGE, MAX, MIN, IF); and named ranges. All ages are welcome.

Class Length One and one-half (1?) hours

Introduction After word processing, spreadsheets are probably the most important and widespread computer application. In fact, the first two `killer applications' (an application regarded as so necessary or desirable that it sells the larger product which it uses) for personal computers were spreadsheet applications: VisiCalc for the Apple II and Lotus 1-2-3 for the IBM PC.

Originally designed to replace accountants' columnar paper, spreadsheets have become the norm wherever tabular data needs to be built, organized, and shared.

Modern spreadsheets have hundreds of functions available to perform almost any imaginable calculation ? far too many to cover in this brief session. In fact, forty hours of classes would not be sufficient to cover all the functions. But most users have no need for more than a handful of functions. They learn the functions they need and ignore the rest. The goal here is to understand how functions work, how to use them, how to discover other functions, and how to apply the understanding of functions to newly found functions.

This class is targeted at users with at least a basic knowledge of spreadsheets and is intended to provide an introduction to using functions and creating formulas. The focus is on Microsoft Excel and Google Drive Spreadsheets, but the lessons can be applied to other spreadsheet applications.

Objectives Learn how functions work Learn how to use functions Learn how to find functions Learn how to build formulas

This manual is a handout for you to keep. Please feel free to use it for taking notes.

Before We Begin: Please open Microsoft Excel and, if you wish, open Google Drive (drive.). If you don't already have a Google account, you can go to accounts.SignUp and register an existing e-mail address (click on the I prefer to use my current email address link) or create a free address; or, if you prefer, ask for the User ID and password for one of the genevalibraryclass(number)@ accounts -- (please don't change the password!).

If you want to do the exercises in just Google Drive Spreadsheets, that is fine; if you want to do the exercises in just Microsoft Excel, that is also okay.

1

*

Working with dates & time

When Lotus 1-2-3 introduced date calculations, it made a blunder: it programmed the year 1900 as a leap year. It wasn't. It numbered 1 Jan 1900 as 1, 2 Jan 1900 as 2, and so forth. 28 Feb 1900 was numbered 59, the non-existent 29 Feb 1900 was numbered 60, and 1 Mar 1900 became 61. By the time the error was noticed, many spreadsheets were using date calculations so the mistake was never fixed. Subsequent spreadsheet programs, in order to preserve compatibility with existing spreadsheets, handle this in different ways. Microsoft Excel simply copied Lotus 1-2-3: dates prior to 1 Mar 1900 are incorrect, and it cannot handle any date prior to 1 Jan 1900. Google Drive Spreadsheets made 30 Dec 1899 a 0 which makes all dates calculate correctly, but makes it incompatible with Microsoft Excel and Lotus 1-2-3 for dates prior to 1 Mar 1900. Some spreadsheets, (e.g. the Calc application in LibreOffice, Apache OpenOffice, or NeoOffice) make 30 Dec 1899 a 0 and dates prior to that negative values (it can handle dates back to -- historians and genealogists, take note! -- 1 Jan 0001).

The good news? Most of us have no need to calculate with dates that long ago and we can ignore these details. However...

Macs: the 1904 system. Spreadsheets built for the earliest Macintosh computers used a similar system, but began with 1 Jan 1904. Spreadsheets for modern Macs still use this system, including Excel. When exchanging spreadsheets with Mac users, if your dates are off by four years and one day, you'll need to take these steps.

Click on the File tab.

This opens the `Backstage'. Click on Options. This opens the Excel Options dialog box. Click on Advanced.

2

*

Scroll nearly all the way to the bottom and find the section labelled When calculating this workbook: and place a check (by clicking on the box) next to `Use 1904 date system'.

Click the OK button and everything should be correct.

You can also take these steps with a spreadsheet you plan to share with a Mac user.

Since each day is calculated as a whole number, times are calculated as fractions: half-way through the day (12 noon) is 0.5; one-quarter through the day (6:00 a.m.) is 0.25; three- quarters through the day (6:00 p.m.) is 0.75. All times are calculated in this manner.

Let's do some calculations with dates.

Microsoft Excel

1. Enter a date in the past in one of the cells.

2. Enter today's date in a different cell.

3. Move to a third cell and

= a. Press (equal sign) ? this tells Microsoft

Excel you want a calculation done (a formula).

Current Date Shortcut:

F+;

Current Time Shortcut:

FH+:

As soon as you press the equal sign, the area between the Name Bar and the Formula Bar

b. Move to the cell with the later date using either the arrow keys or by clicking on the cell with the mouse.

- c. Press (minus sign).

d. Move to the cell with the earlier date.

changes:

e. Either click the between the Name Box

J and the Formula Bar or press .

The result is the number of days between the two dates.

4. Move to a fourth cell and enter a whole number.

J adding an (cancel), (OK / ) and an

(link to functions).

3

*

5. Move to a fifth cell and

= a. Press (equal sign).

b. Move to the cell with today's date.

+ c. Press (plus sign).

d. Move to the fourth cell (the one with the whole number).

J e. Either click the between the Name Box and the Formula Bar or press .

The result shows what the date will be in the number of days you entered in the fourth cell.

Google Drive Spreadsheets

1. Enter a date in the past in one of the cells. 2. Enter today's date in a different cell. 3. Move to a third cell and

= a. Press (equal sign) ? this tells Google Drive Spreadsheets you want a

calculation done (a formula) and the top level items in the menu will grey out.

Current Date Shortcut:

F+;

Current Time Shortcut:

FH+:

b. Move to the cell with the later date using either the arrow keys or by clicking on the cell with the mouse.

- c. Press (minus sign).

d. Move to the cell with the earlier date.

J e. press .

The result is the number of days between the two dates.

4. Move to a fourth cell and enter a whole number.

5. Move to a fifth cell and

= a. Press (equal sign).

b. Move to the cell with today's date.

+ c. Press (plus sign).

d. Move to the fourth cell (the one with the whole number).

J e. press .

The result shows what the date will be in the number of days you entered in the fourth cell.

4

*

Functions

If you want a spreadsheet to calculate always using the current date, it would be quite inconvenient to have to open a spreadsheet and manually replace dates. It would be a lot more convenient to have the spreadsheet automatically use the current date. That is where one turns to functions.

Functions are preset formulas and, like all formulas, they begin with the equal sign. Following the equal sign is the function name; followed by an opening parenthesis, then arguments (zero or more, separated by commas), and completed with a closing parenthesis ? like this:

=FUNCTION(argument1,argument2,argument3,...)

TODAY function

Microsoft Excel

1. In Microsoft Excel, switch to the Formulas Ribbon:

2. Since a formula for today's date is obviously a Date & Time function, click on the Date & Time icon to reveal a list of date and time functions:

3. Move the mouse down to TODAY and click on it. (Note: when hovering [before clicking] over a function, a pop-up appears explaining what the function does.)

This will produce a Function Arguments dialog box:

When a function requires arguments, they will be displayed in the dialog box with a place to provide the information. The TODAY function has no arguments, so there are no fields to be completed. The Function Arguments dialog box displays the result, but here we see the result is `Volatile' ? not to worry, it just means the result is changeable ? which is exactly what we want, something that changes as the date changes. All Function Arguments dialog boxes include a link for Help on this function which will open a detailed explanation of the selected function, its arguments (if any), and examples. This is a gold mine of information.

In both the active cell and in the Formula Bar we should see =TODAY(). This follows the format explained above, with nothing between the parentheses because this function has no arguments.

4. Either click the OK button of the Function Arguments dialog box or click

J the between the Name Box and the Formula Bar or press to complete the formula.

5

*

Google Sheets

To create a Google Drive Spreadsheet: log in to Google Drive, click on Create to open the drop-down men, then click on Spreadsheet.

In Google Drive Spreadsheets, there are two ways to access functions: (1) from the menu (Insert, Function, [SUM, AVERAGE, COUNT, MAX, MIN functions] More [to access other functions]); and (2) from the icon bar (SUM, AVERAGE, COUNT, MAX, MIN functions and More functions...).

Both the More from the menu and the More functions from the icon bar open a new web page...

6

*

The core of this page is a table with columns for Type (Google's categories), Name (of the function), an unlabelled column which shows the function with its arguments, and Description. Each column can be sorted (AZ or ZA). Just above the table is a drop-down menu which can be used to select a single category of functions or all categories of functions.

Use the drop-down (Narrow by...) menu ...

Narrow by: You can enter keywords into the box and the most likely functions will appear. It can be used in conjunction with the drop-down menu or in place of it.

... to display only Date functions (which include Time functions),

... then scroll down until you find the TODAY function (next page).

7

*

Unfortunately, there is no way to select a function and insert it into a formula. You can copy a function's name and paste it into a formula.

F F Don't forget you can use + to

search the text on the web page.

Entering Functions Directly (both Microsoft Excel and Google Sheets): Once you become familiar with a formula, you'll probably want to just enter it directly into a cell, bypassing all the other steps. To enter

=TODAY()J the TODAY function, type

As you begin to type, both Google Drive Spreadsheets and Microsoft Excel pop up a tiny message to assist you.

J In Google Drive Spreadsheets, pressing will insert

the function and move you to the first argument. In Microsoft Excel, you need to use the mouse to double-click on the popup.

8

*

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download