آموزش Google Spreadsheets-En

[A M I R]

[h=2]Creating Simple Formulas

[h=3]Creating Simple Formulas
A convenient and time-saving feature of Google Spreadsheets is its ability to

add, subtract, multiply, and divide numerical information for you. Spreadsheets

uses mathematical expressions called formulas that make handling these calculations easy. In this lesson, we

are going to focus on formulas that contain one mathematical operator

Most of the time you will be using a cell's address in the formula. This is called using

a cell reference. The advantage of using cell references is that you can change

a value in a referenced cell and the formula will automatically recalculate. Using

cell references in your formulas will make sure the values in your formulas are accurate




​

 

[A M I R]

[h=2]Creating Simple Formulas

[h=4]Mathematical Operators

Spreadsheets uses standard operators for formulas, such as a plus sign for

addition (+), a minus sign for subtraction (-), an asterisk for

multiplication (*), a forward slash for division (/), and a caret (^) for exponents


All formulas must begin with an equal sign (=). This is because the

cell contains, or is equal to, the formula and the value it calculates


​

 

[A M I R]

[h=2]Creating Simple Formulas

[h=4]Using Cell References
When a formula contains a cell address, it is using a cell reference. Creating a

formula with cell references is useful because you can update the numerical

values in cells without having to rewrite the formula


By combining a mathematical operator with cell references, you can create a variety of

simple formulas in Google Spreadsheets. Formulas can

also include a combination of a cell reference and a number


​

 

[A M I R]

[h=2]Creating Simple Formulas

[h=3]Creating Formulas

In our example, we will use simple formulas and cell references to help calculate a classroom budget.




​

 

[A M I R]

[h=2]Creating Simple Formulas

[h=4]To Create a Formula:
Select the cell that will display the calculated value (B17, for example).


Type the equal sign (=).

Type the cell address of the cell that you wish to reference first in the formula

(B15, for example). A dotted border will appear around the cell that is being referenced


Type the operator you wish to use. For example, type the addition sign (+).


Type the cell address of the cell that you wish to reference second in the formula (B16, for example).


Press the Enter key on your keyboard, the formula calculates and Spreadsheets displays the result.


To see how the formula recalculates, try changing the value in either B15 or B16. The

formula in B17 automatically displays the new value


​

 

[A M I R]

[h=2]Creating Simple Formulas

[h=4]To Create a Formula Using the Point and Click Method:

Rather than typing cell addresses, you can also point and click on

the cells you wish to include in your formula


Select the cell that will display the calculated value (E18, for example).


Type the equal sign (=).

Click on the cell that you wish to reference first in the formula

(for example, E16). The address of the cell appears in the formula


Type the operator you wish to use in the formula. For example, type the multiplication sign (*).

Click on the cell that you wish to reference second in the formula

(for example, E17). The address of the cell appears in the formula


Press the Enter key on your keyboard. The formula will be calculated and the value will appear in the cell.


​

 

[A M I R]

[h=2]Creating Simple Formulas

[h=4]To Edit a Formula:
Sometimes you may want to modify an existing formula. In our

example, we typed an incorrect cell address in our formula, so we need to correct it


Double-click on the cell that contains the formula you wish to edit. The formula will be displayed in the cell.


Make the desired edits to the formula. In our example, we will replace C21 with B22.


When finished, press the Enter key on your keyboard. The formula

recalculates and the new value displays in the cell


​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=3]Introduction

Google Spreadsheets can help you keep track of your household budget, checking

account, business inventory, company finances, and more. To create these types of

spreadsheets, you will need to understand complex formulas and the order of operations


In this lesson, you will learn how Google Spreadsheets follows the order of

operations to solve complex formulas. You will also learn how to create complex formulas


​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=3]Complex Formulas
A simple formula is a mathematical expression with one operator, such as 7+9. A complex formula

has more than one mathematical operator, such as 5+2*8. When there is more than one operation

in a formula, the order of operations tells us which operation to calculate first. In order to use Google

Spreadsheets to calculate complex formulas, you will need to understand the order of operations





​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=4]Order of Operations

Google Spreadsheets calculates formulas based on the following order of operations:


Operations enclosed in parentheses

Exponential calculations (3^2, for example)

Multiplication and division, whichever comes first

Addition and subtraction, whichever comes first


A mnemonic that can help you remember the order is Please Excuse My Dear Aunt Sally



​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=4]Example 1
The following example demonstrates how to use the order of operations to calculate a formula:


Following the order of operations

​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=4]Example 2
This example demonstrates how Google Spreadsheets solves a complex formula using the

order of operations. The complex formula in cell C8 calculates the sales tax by adding the

prices together and multiplying by the 5.5% tax rate (which is written as 0.055


Entering a complex formula


Google Spreadsheets follows the order of operations and first adds the values

inside the parentheses: (C5+C6+C7) = $14.37. Then, it multiplies by the

tax rate: $14.37*0.055. The result will show that the tax is $0.79




It is important to enter complex formulas with the correct order of operations. Otherwise, Spreadsheets

will not calculate the results accurately. In our example, using the parentheses is very

important, otherwise multiplication is calculated first and the result is incorrect


Result of an incorrect formula

​

 

[A M I R]

[h=2]Creating Complex Formulas

[h=4]To Create a Complex Formula Using the Order of Operations:
In our example invoice below, we will use the order of operations to create a formula

that calculates a subtotal. Our formula will add the available items and the

backordered items, then it will multiply by the price


Select the cell that will display the calculated value and type the equal sign (=).


Enter your formula. In our example, we will type (C5+D5)*E5. This formula

will add the available items and backordered items first because they

are enclosed in parentheses, and then it will multiply by the price


Press the Enter key on your keyboard. The formula calculates and

Spreadsheets displays the result. Double-check your formula for accuracy


The result

​

 

[A M I R]

[h=2]Types of Cell References

[h=3]Introduction
When creating a formula that contains cell references, knowing which

type of cell reference to use will help your formula remain correct when it is copied to a new cell


In this lesson, you will learn about relative and absolute cell references and how to create and copy formulas that include them.


​

 

[A M I R]

[h=2]Types of Cell References

[h=3]Types of Cell References

In order to maintain accurate formulas, it is necessary to understand how

cell references respond when you copy or fill them to new cells in the worksheet


Google Spreadsheets will interpret cell references as either relative or absolute. By

default, cell references are relative references. When copied or filled, they change

based on the relative position of rows and columns. If you copy a formula from

row 1 (for example, =A1+B1) into row 2, the formula will change (for example, to =A2+B2



Absolute references, on the other hand, do not change

when they are copied or filled and are used when you want the values to stay the same



​

 

[A M I R]

[h=2]Types of Cell References

[h=3]Relative References
Relative references can save you time when you are repeating the

same kind of calculation across multiple rows or columns



In our example below, we are creating a formula with cell references in

row 5 to calculate the total cost of the grocery, utility, and rent expense

for each month (B5=B2+B3+B4). For the upcoming months, we want to

use the same formula with relative references (C2+C3+C4, D2+D3+D4, E2+E3+E4, etc.). For

convenience, we will copy the formula in B5 into the rest of row 5 and Google Spreadsheets

will calculate the value of the expenses for those months using relative references




​

 

[A M I R]

[h=2]Types of Cell References

[h=4]To Create and Copy a Formula Using Relative References:
Select the first cell where you wish to enter the formula (for example, B5).

Enter the desired formula (for example, =B2+B3+B4).


Press the Enter key on your keyboard. The result will display in the cell.


Select the cell containing the formula you wish to copy (for example, B5). The fill handle will appear.


Hover the mouse over the fill handle and the cursor will change into a black cross .


Click, hold and drag the fill handle over the cells you wish to fill. A black dotted line appears around the cells.


Release the mouse. The formula is copied to the selected cells as a

relative reference and the values are calculated in each cell


Double-click on several filled-in cells to check their formulas for

accuracy. The relative cell references should be different for each cell


Double-clicking to view the formula

​

 

[A M I R]

[h=2]Types of Cell References

[h=3]Absolute References

There may be times when you do not want a cell reference to change when

copying or filling cells. You can use an absolute reference to keep a row and/or column constant in the formula


An absolute reference is designated in the formula by the addition of a

dollar sign ($). It can precede the column reference, the row reference, or both


The three types of absolute references


You will most likely use the $A$2 format when creating formulas

that contain absolute reference. The other two formats are used much less often.

​

 

[A M I R]

[h=2]Types of Cell References

[h=4]To Create and Copy a Formula Using Absolute References:
In our example, we will use the 5.5% sales tax rate (which is written as 0.055) in cell H14 to

calculate the sales tax for all of the items in column G. In our formula, we need to use the

absolute cell reference $H$14. This will make sure each cell will reference H14 by keeping

the values of both the row and column the same when the formula is copied to other cells in column G


Select the first cell where you wish to enter the formula (for example, G5).

Enter the desired formula (for example, =F5*$H$14).


Press the Enter key on your keyboard. The result will display in the cell.


Select the cell you wish to copy (for example, G5). The fill handle will appear.


Click, hold and drag the fill handle over the cells you wish to fill.


Release the mouse. The formula is copied to the selected cells and the values are calculated in each cell.


Double-click on several filled-in cells to check their formulas for accuracy. The

absolute reference should be the same for each cell


​

 

[A M I R]

[h=2]Working with Functions

[h=3]Introduction
Google Spreadsheets provides a variety of common functions that can be useful

for quickly finding the sum, average, count, maximum value, and minimum

value of a range of cells. Spreadsheets also provides access to hundreds of functions

for financial, statistical, and other complex calculations

In this lesson, you will learn the basics of how to insert a function into a

sheet by typing its name and by using the Functions button. You will also

learn how to access the list of available functions


​