Download MS Excel, Operations and Functions - Introduction to Computing | CPSC 110 and more Study notes Computer Science in PDF only on Docsity!
MS Excel
Operations & Functions
Dr. Lonnie E. Cheney
CPSC 110. Introduction to Computing
2
Operations
- Operations calculate values in a specific order.
- They begin with an equal sign (=).
- After = are the elements to be calculated (operands) , which are separated by operators.
= 3 + 5 – 2 + 4 operands
= 3 + 5 – 2 + 4 operators
- Formulas are calculated from left to right , according to a specific order for each operator in the formula.
3
- Operators specify the type of calculation to perform.
Operations
= 50 + 10 / 2 - 5 is equal to 50 …not 25 ???
4
Higher
Lower
• Precedence
Operations
50 + 10 / 2 - 5 = 50 …not 25 ???
7
Functions
- A function is a predefined formula for a common calculation.
SUM ( argument1 , argument2 , … )
- Each function has a name and syntax.
- Syntax specifies:
- the order and type of the different arguments , and
- the location of commas, parentheses, and other punctuation.
SUM ( arg1 , arg2 , … ) arguments
SUM ( arg1 , arg2 , … ) punctuation
- Arguments can be:
- numbers,
- text, or
- cell references.
8
- SUM is a commonly used mathematical function.
- To add a small number of cells we could type:
= A1 + A2 + A3 + A
…it would be hard for many cells!
- SUM allows to total the values in a range of cells:
= SUM( A1:A100 )
- Functions within functions.
= ROUND( AVERAGE( A1:A100 ) , 1 )
- It calculates the average of the values in cell A1 to A
- It rounds this result to 1 decimal point.
Functions
9
- Excel has 300+ functions organized into categories :
• Financial
• Date and Time
• Math
• Statistical
• Lookup
• Database
• Text
• Logical
• Information
Functions
10
Functions
13
Functions
- Exploring! (with the “Insert Function” dialog)
14
Functions
- Exploring! (with the “Insert Function” dialog)
15
Functions
C8 Relative Reference It changes row/column reference according to row/column displacement.
$C$8 Absolute Reference It does not change with displacement. It always point to the same cell (to C8).
C$8 Mixed Reference (fixed row) Column changes with displacement. Row fixed (to 8)
$C8 Mixed Reference (fixed column) Row changes with displacement. Column fixed (to C).
