CSE

COUNTIF function, what is it and whats the order

Counts the number of cells in a range that meet a

specific criteria

=COUNTIF(range, criteria)

Range: A continuous range (e.g., B2:D4)

Criteria: Determines what cells to count

(e.g., “>20”)

IF function order

=IF(condition, value if true, value if false)

the rule for text

use quotient when you want to treat something as words

what is a boolean function

something that gives you the result of true or false

hard coded boolian expression

when you want excel to check a condition:

>=5

<10

=7

you need quotes

wildcard- what is it, how do you use it

Lets you search for text that starts with, ends with, or contains something

“USA*”

the astrick means anything can come after this

cell referencing

greater than or equal to A2

(C2:C3,”>=”&A2)

you need quotes

The biggest thing to memorize

No quotes:

• Numbers → 5

• Cell references → A2

Quotes:

• Text → "USA"

• Boolean expression → ">=5"

Quotes + &:

• Boolean expression + cell reference → ">="&A2

Countifs

Counts the number of items in a range (using multiple criteria and multiple ranges)

countsifs vs countif plus countif

ex. how many people are from usa or canada?

countif plus countif

how many people are from the us and over 20? (two columns)

coountifs

SUMIF

Find the things that meet my condition, then add up the numbers that go with them.

what does a cell contain

Cells contain text, numbers, or Boolean

values

Functions

Predefined formulas

Ex. SUM

Order of precedense

PEMDAS

does formatting a cell change the number stored?

no, this just changes what it looks like

Ex. rounding 10.565 to 10.57

Excel errors:

#####

#DIV/0!

#NUM!

#REF!

#VALUE!

##### Numeric value too wide to display

#DIV/0! Divide by 0 or blank cell occurs

#N/A Data being referenced is not available

#NAME? Text in formula is not recognized

#NUM! Problem with a number in a formula or

function

#REF! Cell reference is not valid

#VALUE!Wrong type of argument or operand in a

formula (info given isnt the right type)

Blank cells

certain functions ignore them instead of treating it as 0

what does ctrl + do

shows you formulas

inserting a row

if you insert a row for a function (aka a range) it will automatically add it. If it is just a normal formula, it won’t

ways to type a function

Manually

• Type the function into the formula

• AutoSum button

• Find in Formulas tab

• Also, in Home tab

• Function Wizard

• Find in Formulas tab

• Also, in formula bar

SUM(number1,[number2],…)

AVERAGE(number1,[number2],…) s

MIN(number1,[number2],…)

MAX(number1,[number2],…)

COUNT(value1,[value2],…)

COUNTA(value1,[value2],…)

Adds the numbers in a range of cells

Calculates the arithmetic mean of a list of values

Returns the smallest number of a range of values

Returns the largest number of a range of values

Determines the number of cells in a range that contain

numbers

Counts non-blank cells (anything, even text)

ROUND

(number/cell, number of digits)

Cell referencing

Relative

=A1

Mixed

=A$1 or =$A1

Absolute

=$A$1

Named ranges

naming a cell or range and using it in functions

referencing cells from another sheet

To reference B3 from Sheet2: Sheet2!B3

• If the sheet name has spaces or special characters,

use single quotes: ‘Sheet 2’!B3

• Notice the use of the exclamation point (!) –

separates the sheet name from the cell referenc

When you translate a problem and encode it into a spreadsheet, the first thing you should do is consider using absolute cell referencing.

false

what does the SUM function do with text

ignores it

countif function requirements

SUMIF

Sums the number of items in a range that meet a

specific criteria

Chapter 4: The SUMIF Function

14

=SUMIF(criteria_range, criteria, [sum_range])

first what genre, then what is has to be, then the actual numbers its adding

AVERAGEIF

Averages the number of items in a range that meet

a specific criteria

Chapter 4: The AVERAGEIF Function

18

=AVERAGEIF(criteria_range, criteria, [average_range])

SUMIFS

Sums a range (using multiple criteria and multiple

ranges) that meet a specific criteria

Chapter 4: The SUMIFS Function

19

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],

…)

AVERAGEIFS

Averages a range (using multiple criteria and

multiple ranges) that meet a specific criteria

Chapter 4: The AVERAGEIFS Function

22

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],

…)

• All criterion must be true for the cell to

Large and Small Functions

Returns the largest value in the range, based on

the “k” argument

Chapter 4: Book & Lecture Only

23

=LARGE(array, k)

• Array: A continuous range

• k: The position (from the largest) in the array

(range)

Rank.eq

Returns the rank of a number in a list of numbers

=RANK.EQ(number, ref, [order])

Order: A number specifying how to rank the

numbers

– 0 (default): Ranks in descending order

– 1: Ranks in ascending order

SUMPRODUCT

Multiplies corresponding components in the given

arrays, and returns the sum of those products

Chapter 4: Book & Lecture Only

27

=SUMPRODUCT(array1, [array2], [array3], …)

Boolean functions: syntax

Relational

Operator

=A2>B

5

>

<

=

<=

>=

<>

Boolean Functions: AND(logical1, [logical2], …)

Returns TRUE if all arguments evaluate to true

=OR(logical1, [logical2], …)

Returns TRUE if at least one argument evaluates to true

=NOT(logical)

Changes FALSE to TRUE and TRUE to FALSE