Excel Functions and Formulas

Excel Formulas and Functions

ICT

Pay attention to the next 9 slides.

You will complete a short quiz at the end.

What is a function?

They are provided by Excel to help you perform operations.

What are the functions of a spreadsheet?

• A function is built into the spreadsheet.

• All functions allow simple or complex operations.

• Each function has specific rules that need to be followed.

Functions are written like code.

=AVERAGE (D4:D14) / calculates the average of the numbers from cell D4 to D14.

Several types of functions

• Logical

• Text

• Financial

• Date and Time

• Math

What is a formula?

An equation that is used to perform a calculation.

Formulas

Always start with an equal sign.

followed by values or operators such as the +, -, *, / signs. =D4+D5 will add the numbers 15+17 to make 32.

Multiple Choice

How does a function work in a spreadsheet?

• Accepts values, performs calculations and displays a chart.

• Accepts values, performs calculations and displays a result.

• Performs calculations, displays results and accepts numbers.

Multiple Choice

Which are the three essential elements to enter a function?

• Equal sign, function name and cell reference.

• Function name, data and cell reference.

• Equal sign, cell reference and formula.

Multiple Choice

These are examples of types of functions in a spreadsheet:

• Function sum, function currency and function count.

• Text functions, function currency and logical functions.

• Logical functions, text functions and financial functions.

Multiple Choice

=C4+C5

What is the result?

• 32

• 12

• 17

Multiple Choice

A function that calculates the total from the adjacent cell through the first non-numeric cell using SUM function in its formula

• AVERAGE function
• AutoSum
• COUNT function
• MIN function

Multiple Choice

A function that returns the largest values in a set of values

• MAX function
• AVERAGE function
• MIN function
• SUM function

Multiple Choice

A function that determines the minimum value in a range of cells

• MIN function
•  MAX function
•  SUM function
• AVERAGE function

Multiple Choice

A predefined formula that performs a calculation

• Function
• AutoSum
• Value
• Variable

Multiple Choice

A function that totals all of the cells in a range

• SUM function
• MIN function
• MAX function 
• AVERAGE function

Multiple Choice

Write a FORMULA or FUNCTION for the following:

Add cells A1 and B1

• A1 + B1

• =A1 + B1

• =SUM(A1+B1)

• SUM(A1+B1)

Multiple Choice

Write a FORMULA or FUNCTION for the following:

Add a range of cells C4 to C15

• =SUM(C4:C15)

• SUM(C4:C15)

• =SUM C4:C15

• =ADD(C4:C15)

Multiple Choice

Write a FORMULA or FUNCTION for the following:

Multiply cells A1 and B1

• A1 * B1

• =A1 * B1

• MULTIPLY(A1:B1)

• =A1 x B1

Multiple Choice

Write a FORMULA or FUNCTION for the following:

Find the average of the range of cells C1 through C20

• AVERAGE(C1:C20)

• =AVG C1:C15

• =AVERAGE C1:C15

• =AVERAGE(C1:C20)

Multiple Choice

Write a FORMULA or FUNCTION for the following:

Find the smallest number in the range of cells C1 through C20

• MAX(C1:C20)

• =LARGEST(C1:C15)

• =MAX C1:C15

• =MIN(C1:C20)

Multiple Choice

Write a FORMULA or FUNCTION for the following:

Subtract 12 from the sum of the range of cells A2 to A10

• =SUM(A2:A10) - 12

• SUM(A2:A10) - 12

• =12 - SUM(A2:A10)

• =SUM(A2:A10)

Multiple Choice

What would be a correct formula for SUM in excel?

• =SUM(B3:B9)
• =SUMB3+B9
• SUM(B3:B9)
• =ADD(B3:B9)

Multiple Choice

Which symbol must all formula begin with?

• =
• +
• (
• @

Multiple Choice

Which of the following formulas is not entered correctly?

• =10+50

• =B7+B8

• =B7+14

• =10+50;

Multiple Choice

Which of these symbols is used to do a division on a computer?

• \

• *

• /

• |

Multiple Choice

Which operator is used to multiply in Excel?

• +

• x

• /

• *

Multiple Choice

Formulas can be copied to adjacent cells with the ____________.

• Fill handle

• PgDn Key

• Function key F4

• None of the above

Multiple Choice

Excel follows the ___________________ and first adds the values inside the parentheses.

• Order of Operations
• Formula Guidelines