Chapter 20 - Spreadsheets

Sum

• =sum (A2:C2) —> A2 + B2 + C2

• =sum (A2,C3) —> A2 + C3

Multiply

• =A2 * B2

Division

• =A2/B2

Exponent

• =A2 ^ 2

Average

• =average (A3:A8)

Maximum

• =max (A3:A6)

Minimum

• =min (A3:A8)

Int (A1)

• Takes the whole number part of a number and ignores all digits after the decimal point.

• 123.56 —> 123

=ROUND (A1, 2)

• Rounds the contents of A1 to 2 decimal places

• 123.578 —> 123.58

=ROUND (A1, 1)

• Rounds the contents of A1 to 1 decimal place

• 123.578 —> 123.6

=ROUND (A1, 0)

• Rounds the contents of A1 to 0 decimal place

• 123.578 —> 124

=ROUND (A1, -1)

• Rounds the contents of A1 to the nearest 10

• 123.578 —> 120

=ROUND (A1, -2)

• Rounds the contents of A1 to the nearest 100

• 123.578 —> 100

Count (B4 : B9)

• Can count for the numbers only

Countif

• =countif (B4 : B9,”apple”)

• Can count the number of cells that contain exactly criteria data

Counta

• =counta (B4 : B9)

• Can count the number and alphabet together not including blank cell

Countblank

• =countblank (C1 : C3)

• Can count the number of blank cells only

Sumif

• =sumif (B1 : B9,” > 9 “)

• Can sum cells based on criteria

IF 

• Contains a pair of brackets and within the brackets, three parts, each separated by comma

• =IF (A1=5, A2*0.05, “No discount”)

• =IF (C3>=10, “Very experienced”, “Not experienced”)

• =IF (A1= “Apple”, “I like it”, “Don’t like”)

NestedIF

• Nested functions means having one function inside another one

• =IF(C3>=10, “Very experienced”, IF(C3>=5, “Experienced”, “Not Experienced”))

• =IF(D10>550, “A”, IF(D10>500, “B+”, IF(D10>300, “C”)))

AND and OR function in NestedIF

• =IF(AND(A3>5, A3<10), “Good”, “Bad”)

• =IF(B14>=1100, “10”, IF(B14>=800, “6”, IF(B14>=500, “4”, “0”)))

Absolute Reference ($)

• If you want to assign the default cell for finding area, you will put absolute reference ($)

• =$A$1 : $D$2

LookUp

HLookUp

• A function that performs a horizontal lookup of data

• =HLookUp (main value, table range, row number, FALSE/TRUE)

• =HLookUp (B4, $A$1 : $D$2, 2, FALSE)

• FALSE = Specific Data

• TRUE = Nearest Data

VLookUp

• A function that performs a vertical lookup of data

• =VLookUp (main value, table range, column, FALSE/TRUE)

• =VLookUp (B4, $A$1 : $D$2, 2, FALSE)

Finding the data from another table using HLookUp or VLookUp

• =(Cell reference, filename! Table range, row or column number, FALSE)

• =VLookUp (A2, classlist.csv! $A$1 : $D$2, 2, FALSE)

IF (Sumif and Countif)

What is the total value of Apple by using the formula in A5.

=SUMIF ($A$1 : $A$3, “Apple”, $B$1 : $B$3)

=SUMIF ($A$1 : $A$3, A6, $B$1 : $B$3)

Types of Sorting

Ascending order : Arranging data from A to Z, numbers from lowest to highest.

Descending order : Arranging data from Z to A, numbers from highest to lowest.

Contains A = A

?A = A followed by any letter