excel exam 1

SUM

=SUM(A2:A10)

Adds all numbers throughout the given cells

AVERAGE

=AVERAGE(A2:A10)

Finds the mean

MIN

=MID(A2:A10)

Finds the smallest number

MAX

=MAX(A2:A10)

Finds the largest number

COUNT

=COUNT(A2:A10)

Counts cells containing numbers

COUNTA

=COUNTA(A2:A10)

Counts cells that are not empty

MEDIAN

=MEDIAN(A2:A10)

Finds the middle value

IF

=IF(B2>=70,” Pass”, “Fail”)

Shows “Pass” If B2 is at least 70, otherwise shows “fail”

COUNTIF

=COUNTIF(B2:B20, “>=70”)

Counts scores that are at least 70

SUMIF

=SUMIF(A2:A20, “East", B2:B20)

Adds values in column B when the matching cell in column A say “East”

AVERAGREIF

=AVERAGEIF(A2:A20, “East”, B2:20)

Averages values in column B when column A says “East”

&

=A2&“ “&B2

Joins two cells with a space, such as a first and last name

LEFT

=LEFT(A2,3)

Takes the first three characters

RIGHT

=RIGHT(A2,3)

Takes the last three characters

TRIM

=TRIM(A2)

Removes extra spaces, leaving single spaces between words

COUNTBLANK

=COUNTBLANK(A2:A20)

Counts empty cells

COUNTIFS

=COUNTIFS(A2:A20, “East” ,B2:B20, ”>100”)

Counts rows where the region is east AND sales exceed 100

SUMIFS

=SUMIFS (C2:C20,A2:A20, “East” ,B2:B20, “Shirts”)

Averages sales for East-region shirts

AVERAGEIFS

=AVERAGEIFS (C2:C20,A2:A20, “East”, B2:B20, “Shirts)

Average sales for East-region shirts