Use Formulas and Functions

Understand Formulas and Functions

Formulas

  • A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value1
  • A formula always begins with an equal sign (=) so the values or data that a formula uses can be found to calculate2
  • Start with equal sign “=”
  • Basic arithmetic
    • Addition “+”
    • Subtraction “-“
    • Multiplication “ * ”
    • Division “/ ”
    • Exponent “ ^ “

 

Functions

  • Start with equal sign “=”
  • Each named or pre-made formulas does a unique task
  • Each function uses at least one set of parentheses
  • General syntax:  =name_of_function()
    • Ex:  =Average()

 

Understand Order Of Operations

1.   Parentheses

2.   Exponents

3.   Multiplication

4.   Division

5.   Addition

6.   Subtraction

 

Understand Cell Reference

  • Cell reference
    • The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that is displayed at the intersection of column B and row 3 is B3
    • Refers to a cell or a range of cells on a worksheet and can be used in a formula
  • In one or several formulas, cell reference used to refer to:
    • Data from one cell on the worksheet
    • Data that is contained in different areas of a worksheet
    • Data in cells on other worksheets in the same workbook

 

Use Formulas

  • Google Docs can perform simple to complex mathematical equations and manipulations on the data in the spreadsheet by using Google spreadsheet formulas
  • If the Formula Bar is not display on under the Formatting Toolbar, go to View menu on the Menu Bar and choose Formula Bar

 

Create Formulas

1.   To create the formula by entering input data manually, do the following

  • Double click on the cell
  • Enter the input data for the formula by using general syntax:

=number_1+number_2+number_3+……



  • Press ENTER to show the result

2.   To create a formulas by using cell reference, do the following:

  • Select a cell to build a formula
  • Click desired cells contained data or enter manually the cell references to create a formula based on a general syntax

=cell_reference1+cell_reference2+cell_reference3+…


  • Press ENTER to show the result

 

Edit Formulas

Do one of the following:

1.   Click on the cell contained the formula and edit the formula on the Formula Bar

2.   Double on the cell contained the formula and edit the formula directly in the cell

 

Use Functions

  • One of most useful features is that creating custom formulas to perform calculations on the data in the spreadsheet
  • Google Spreadsheets are designed with the built-in formulas and functions that make it easy to perform these calculations

 

SUM Function

1.   General syntax: =SUM(number_1,number_2,…… number_30)

  • number_1 (Required) – the first number argument to add
  • number_2 (Optional) – number argument 2 to 30 to add
  • Ex:  =SUM(B5:B12), =SUM(A1,A2,A3)

2.   Select the cell to perform the calculation

3.   Do one of the following:

  • Enter the function name beginning with the equals

NOTE   Google Spreadsheet will starts autocomplete different functions that have the word SUM in them

  • From the Menu Bar, go to Insert menu > Function > choose SUM

  • On the Formatting Toolbar, click Function button > choose SUM

1.   Select the range to calculate total

2.   Press ENTER to see the result

 

AVERAGE Function

1.   General syntax: =AVERAGE(number_1,number_2,…… number_30)

  • number_1 (Required) – the first number argument to average
  • number_2 (Optional) – number argument 2 to 30 to average
  • Ex: =AVERAGE(B5:B12)

2.   Select the cell to perform the calculation

3.   Do one of the following:

  • Enter the function name beginning with the equals

NOTE   Google Spreadsheet will start autocomplete different functions that have the word AVERAGE in them

  • From the Menu Bar, go to Insert menu > Function > choose AVERAGE

  • On the Formatting Toolbar, click Function button > choose AVERAGE

4.   Select the range to calculate average

5.   Press ENTER to see the result

 

Copy a Function

1.   Click on the cell contain the function

2.   Place the mouse on the blue square where the cursor become the AutoFill Handle

3.   Hold the AutoFill Handle and drag to a desired cell to place a copy of the function

4.   The function will be re-calculate with new data range corresponding with where the copy of the function is placed

 

Find Functions

Beside the common built-in functions list under Functions list in Insert menu, Google Spreadsheets contains hundreds of built-in functions that can use to calculate data in the spreadsheet fast and easily. The functions are classified in different categories such as Math, Financial, Logical, Date, Lookup….

1.   From the Menu Bar, go to Insert menu > Functions > click More…

2.   The Google Spreadsheet function list is displayed. Do any of the following:

  • Click the down arrow in the All box to narrow the list by categories
  • Enter keyword in Filter field to filter the list
  • Click on any column headers to sort the table by that column

3.   Example: IF function 

  • General syntax: =IF(statement, value_if_true, value_if_false)
    • value_of_true/false = “text”
    • value_of_true/false = cell reference
    • Ex: =IF(B11>4000, “Good”, “Bad”)

  • Use to test the condition or statement
    • Output one result if the statement is true
    • Output a different result if the statement is false

1,2- Source: Microsoft Office Excel 2010 Help