Please enable JavaScript.
Coggle requires JavaScript to display documents.
07 Spreadsheet Functions - Coggle Diagram
07 Spreadsheet Functions
7.4a Logical Function (ISBLANK, AND ,OR ,IF , NOT, VLOOKUP)
ISBLANK
-checks a specified cell and tells us if it is blank or not.
E.g: =ISBLANK(A2)->FALSE
AND
-returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false.
-E.G.=AND(A2>=50,B2>=50) ->TRUE
OR
- a logical function that outputs TRUE if at least one of the inputs is TRUE, and FALSE only if all the inputs are FALSE.
-E.G.=OR(A2>=50,B2>=50)->TRUE
IF
-used to test whether a single cell or range of cells meets certain criteria in a logical test, where the result is always either TRUE or FALSE.
-E.G.:=IF(A3>=2,"YES","NO") ->YES
NOT
- a handy function that lets you negate a logical value or expression.
-E.G. =NOT(TRUE) ->FALSE
VLOOKUP
-to search for an item in a column and return data from that row if a match is found.
-E.G. =VLOOKUP(85, A2:C8, 2) ->B
7.5b Text Functions 1
Lower, Upper, Proper, Len, Trim, Char, Text, Value
1.Lower e.g. LOWER = lower
- Upper e.g. upper = UPPER
- Proper e.g. =PROPER(hello) = Hello
- Len function counts the length of the word e.g. True Light = 10
- Trim function removes the extra spaces between words
e.g." True Light " = "True Light"
- Char function changes numbers into characters e.g. =CHAR(65) = A
- Text e.g. =TEXT(4846,"$0.00" ) = $4846.00
- Value function gives the value of the number e.g. =VALUE("2/12") = 44897
7.2 Statistical Functions (MAX, MIN, AVERAGE, COUNT, COUNTA, COUNTBLANK, COUNTIF, RANK)
COUNT: Count the number of numeric values in a dataset
e.g = COUNT(A1:A6) returns 2
-
-
-
-
COUNTIF: Returns a conditional count across a range
e.g =COUNTIF(A1:A9,"a") returns 1
MAX: Returns the maximum value in a numerical dataset
e.g =MAX(A1:A5) returns 5
RANK: Find the order of a number in a range
e.g The formula in cell B2 is =RANK(A2, A$2:A$6,1)
7.1 Basic operators
Maths operators
+, -, *, /, ()
Relative cell address
- When formula is copied downwards, the numbers in cell address automatically change (e.g. A1 to A2)
- When formula is copied horizontally, the letters automatically change (e.g. A1 to B1)
Absolute cell address ($)
- if we don't want a cell address to change during copy, we use $ before the letter or the number to lock it.
- e.g. if A$1 is copied downwards, it remains A$1.
- e.g. if $A1 is copied horizontally, it remains $A1.
- e.g. if $A$1 is copied both downwards and horizontally, it remains $A$1.
7.3 Mathematical Functions (SUMIF, RANK, SQRT, RAND, INT, RANDBETWEEN)
-
RAND: Returns a random number between 0 inclusive and 1 exclusive (or 0.999999...)
e.g =INT( RAND()* 30 + 1 ) generates a random number between 1 and 30 inclusively
-
SUMIF: Returns a conditional sum across a range
e.g =SUMIF(A2:A12, "4A", B2:B12) returns 15
-
RANDBETWEEN: Returns a random number between 2 values
e.g =RANDBETWEEN(1,10) returns 3
ROUND: Rounds a number to a certain number of decimal places
e.g =ROUND(23.456, 2) returns 23.46
7.6a,b VLOOKUP, Pivot Table and Pivot Chart
VLOOKUP = Vertical lookup
- It searches a specified column vertically for a search key and then returns the value you're looking for from the same row.
Pivot table and chart
2.A pivot table is a summary of your data, which you can use to make a pivot chart.
7.5d Text Function 2 (CONCATENATE & LEFT, RIGHT,MID, FIND)
-
-
Mid
-a text function that is used to extract a special middle part of a string.
Right
-extracts a specific number of characters from the right side of a text value and returns that fraction.
-
-