Exam 1 Review Flashcards by Serina Gurm

Function
Use: the names listed on tabs of the worksheet

Worksheet Names

How well did you know this?

Not at all

Perfectly

They do not need to be listed in the formula referencing cells within the same worksheet. They are needed when referencing cells in different worksheets

Worksheet Names

How well did you know this?

Not at all

Perfectly

What must the worksheet be encased in when they include one or more spaces

’ ‘ and include ! after
=’Worksheet Name’!Cells

How well did you know this?

Not at all

Perfectly

Function
Use: Formulas should be set up in such a way as to make creating formulas easy so they can be copied instead of having to be rewritten over and over

Cell Referencing

How well did you know this?

Not at all

Perfectly

Cell with the information

Cell Referencing

How well did you know this?

Not at all

Perfectly

Single cell reference

How well did you know this?

Not at all

Perfectly

A2:A9

Range along a column

How well did you know this?

Not at all

Perfectly

A2:H2

Range along a row

How well did you know this?

Not at all

Perfectly

A:A

Range along the entire column

How well did you know this?

Not at all

Perfectly

A2:C8

Block Range

How well did you know this?

Not at all

Perfectly

$ locks the referenced item preventing it from changing

Mixed Cell Reference

How well did you know this?

Not at all

Perfectly

$A2

Locks the Column
Mixed Cell Reference

How well did you know this?

Not at all

Perfectly

A$2

Locks the row
Mixed Cell Reference

How well did you know this?

Not at all

Perfectly

Exact Cell

Absolute Cell Reference

How well did you know this?

Not at all

Perfectly

$A$2

Locks the column and row
Absolute Cell Reference

How well did you know this?

Not at all

Perfectly

Function
Use: Use a name for cell reference. Treated as an absolute cell reference

Named Ranges

How well did you know this?

Not at all

Perfectly

Best to use when complex and/or long cell references. Also, helps with remembering the purpose of the formula

Named Ranges

How well did you know this?

Not at all

Perfectly

Sales Tax instead of “A2*1.075”

Named Ranges

How well did you know this?

Not at all

Perfectly

Function
Use: Pre- determine if you want __ ___ to be included in your calculation

Blank Cells

How well did you know this?

Not at all

Perfectly

Included: Fill with 0 (zero)
Excluded: Leave Blank

Blank Cells

How well did you know this?

Not at all

Perfectly

Syntax
Use: when using text items in cell references

Text

How well did you know this?

Not at all

Perfectly

For Text what words do we not need to put quotes are

AND, OR, NOT, TRUE, FALSE

How well did you know this?

Not at all

Perfectly

Syntax
Use: Cell references larger than

Greater Than

How well did you know this?

Not at all

Perfectly

What 3 functions can be used for Greater Than

Sumif(s), Countif(s), and Averageif(s)

How well did you know this?

Not at all

Perfectly

How to write something is greater than A2 in a function

">"&A2

How to write something is greater than and equal to A2 in a function

">="&A2

Hard Coded Greater than 2 In a function

">2"

Greater than A2 In a formula

>A2

Greater than or equal to In a formula

>=A2

Syntax Use: Cell References small than

Less Than

What 3 functions can be used for Less Than

Sumif(s), Countif(s), and Averageif(s)

How to write something is less than A2 in a function

"<"&A2

How to write something is less than and equal to A2 in a function

"<="&A2

Hard Coded Less than 2 In a function

"<2"

Hard Coded Less than or equal to In a formula

"<=2"

Less than A2 In a formula

Less than or equal to In a formula

<=A2

Syntax Use: Has to be broken into an AND formula with each range defined separately. You cannot write: =50>=E2<=80

Between

How to write =50>=E2<=80 in a function

=AND(E2>=50,E2<=80)

Syntax Use: To denote a character that will match any character or sequence of characters in a search

Wild Card

Rules of Wildcard, you need an ______ within ""

Asterisk, *

Formula Use: adds the numbers in a range of cells

Sum

How to add the range of A2:A9

=SUM(A2:A9)

How to add the cells A2,A5,A7

=SUM(A2,A5,A7)

Formula Use: Calculates the arithmetic mean of a list of values

Average

How to find the average of (A2:A5)

=AVERAGE(A2:A5)

Formula Use: Returns the smallest number of a range of values

Min

Formula Use: Returns the largest number of a range values

Max

What function to use when you need the smallest number in a range?

Min

What function to use when you need the largest number in a range

Max

Formula Use: Determines the number of cells in a range that contain numbers

Count

What function to use when you need to count the numbers in a range

Count

Does the Count formula ignore blank/empty cells

Yes

Formula Use: Determines the number of cells in a range that are not empty

CountA

What function to use when you need to count all the characters in a range

CountA

Does the CountA formula ignore blank/empty cell

Yes

Formula Use: Rounds a number to specified number of digits. When nesting use before other formulas

Round

For Round Function, what does -2 =

Nearest Hundreds

For Round Function, what does -1 =

Nearest Tens

For Round Function, what does 0 =

Nearest Whole Number

For Round Function, what does 1=

Nearest Tenth

For Round Function, what does 2 =

Nearest Hundreths

Conditional Statistical Functions Use:Counts the number of items in a range that meets a specific criteria

COUNTIF

Conditional Statistical Functions Use: Counts the number of items in a range that meet a specific criteria for multiple criteria and ranges

COUNTIFS

Conditional Statistical Functions Use: Sums the number of items in a range that meet a specific criteria

SUMIF

Conditional Statistical Functions Use: Sums the number of items in a range that meet a specific criteria for multiple criteria and ranges

SUMIFS

Conditional Statistical Functions Use: Averages the number of items in a range that meet a specific criteria

AVERAGEIF

Conditional Statistical Functions Use: Averages the number of items in a range that meet a specific criteria for multiple criteria and range

AVERAGEIFS

Conditional Statistical Functions Use: Returns a specific value from the largest number in the array

Large

Conditional Statistical Functions Use: Returns a specific value from the smallest number in the array

Small

Conditional Statistical Functions Use: Returns the rank of a number in a list of numbers

Rank.Eq

If you want to rank a list of numbers in a descending order, what needs to be included

0 or omitted number

If you want to rank a list of numbers in a ascending order, what needs to be included

Conditional Statistical Functions Use: Multiplies corresponding components in the given arrays, and returns the sum of those products

SumProduct

In the SumProduct function, are non numeric cells ignored or treated as 0

treated as 0

What answers do Boolean Functions return

True/False

Boolean Functions Use: Returns true if all arguments are true

And

Boolean Functions Use: Returns true if one of the arguements is true

Boolean Functions Use: Returns false when one arguement is false

And

Boolean Functions Use: Returns false when all arguments are false

Boolean Functions Use: Changes false to true and true to false

NOT

Boolean Functions Use: Returns true when the arguments have been met Ex: =Not(Or(F5:F7))

NONE

Boolean Functions Use: Returns true when the arguments have been met Ex: =And(E5>=B2, Not(F8))

ONLY

IF Functions Use: Checks to see if a condition is true or false. If true one thing happens, if false another thing happens

Validates if a value is True or False

IF(True/False)

What to do when you need the cell to say true or false in an IF function ?

=IF(A1,"Cell contains the word True","Cell contains the word False")

IF Functions Use:Series of if formulas - If this is true, do this. else if this is true, do this, else do this. Can be nested up to 64 times

Nested IF

IF Functions Use: Solve in order based upon the highest to lowest or lowest to highest

Embedded IF