Please enable JavaScript.
Coggle requires JavaScript to display documents.
Chapter 7: Spreadsheet functions 4C 24 Abbey Sin 4C 03 Chloe Chan…
Chapter 7: Spreadsheet functions
4C 24 Abbey Sin
4C 03 Chloe Chan
Mathematical
RAND
=RAND()
Result: 0.7420587122
Returns a random number between 0 inclusive and 1 exclusive
INT
=INT(99.44)
Result: 99
Rounds a number down to the nearest integer that is less than or equal to it
RANDBETWEEN
=RANDBETWEEN(1:100)
Result: 53
returns a number randomly generated according to the condition
ABS
=ABS(-2)
Result: 2
Returns the absolute value of a number
ROUND
=ROUND(2.5)
Result: 3
Rounds a number to a certain number of decimal places according to standard rules
RANK
=RANK(A2,A$2:A$5)
Result: 3
ranks the cell in increasing order
SUMIF
=SUMIF(A1:A5,">50")
Result: 237
Returns a conditional sum across a range
SQRT
=SQRT(16)
Result: 4
returns the square root
Logical
ISBLANK
=ISBLANK(A6)
Result: FALSE
checks whether the chosen cell is empty
IF
=IF(A3>=12,"TRUE","FALSE")
Result: TRUE
returns value depending on condition
AND
=AND(A3<12,A5<=3)
Result: FALSE
returns true if all conditions are fulfilled, otherwise false
OR
=OR(A3<12,A5<=3)
Result: TRUE
returns true if either one condition is fulfilled
NOT
=NOT(1<>1)
Result: TRUE
returns the opposite value
VLOOKUP
=VLOOKUP(D3,A$2:C$8,2)
Result: B
Searches down the first column of a range for a key and returns the value of a specified cell in the row found.
Statistical
MAX
=MAX(A1:A7)
Result: 3
returns the max value
MIN
=MIN(A1:A7)
Result: 1
returns the min value
AVERAGE
=AVERAGE(A1:A7)
Result: 4
returns the average value
COUNT
=COUNT(A1:A7)
Result: 3
counts only the number of numerical values
COUNTA
=COUNTA(A1:A7)
Result: 5
counts numbers and text
COUNTBLANK
=COUNTBLANK(A1:A7)
Result: 2
counts blank fields
COUNTIF
=COUNTIF(A1:A7,">2")
Result: 2
counts under the entered condition
