CAT PRACTICALS
Excel:
Functions:
=sum( ) Calculate the sum Time: deduct time
=average( ) Calculate the average Convert to number
=max( ) Calculate the maximum Multiply with 24 to get day
switched to time
=min( ) Calculate the minimum
Divide w/ 365 to get years
=large( ) Largest N number
Divide w/ 30 to get months
=small( ) Smallest N number
=mode( ) Occurs most often
=median( ) Middle value
=count( ) Counts all the values with numbers in
=counta( ) Counts all the values
=countblank( ) Counts all the values that are blank
=power( ) To the power of e.g. 22
=rand( ) “random” number between 0 and 1
=randbetween( ) “random" number between a specific range of numbers
=round( ) rounds the number to the nearest whole number
=rounddown( ) round the number downwards e.g. Rounddown(4.567,2) = 4.56
=roundup( ) rounds the number upwards e.g. Roundup(4.567,2) = 4.57
=int( ) round a number down towards the nearest whole number
=trunc( ) removes the fraction of the number
=day( ) day between 1 and 31
=month( ) month between 1 and 12
=year( )
=days( ) the amount of days between 2 dates
=date( ) date
=today( ) todays date
=hour( ) hour between 1 and 23
=minute( ) minute between 1 and 59
=second( ) second between 1 and 59
=time( )
, =countif( ) only counts values according to specific criteria
=sumif( ) only counts numbers according to specific criteria
=countifs( ) only counts values according to specific criteria’s
=sumifs( ) only counts numbers according to specific criteria’s
Wildcard a placeholder that can showcase a character or a series of characters
* empty string – any number or character
? One exact character
*Note that we only use these at countif and sumif
=if( ) it allows you to make logical comparisons between a value and what you expect
=if( condition, true, false )
Nexted if if inside another if
And Multiple conditions – BOTH must be true
Not Invert criteria – true – false
Or One condition must be true
=vlookup( ) Vertical – top to bottom
=hlookup( ) Horizontal – left to right *remember absolute cell referencing
$ or F4
True approximate match
False exact match
Text functions:
Concatenate combine strings
Len find out how many characters the string has
Upper turns to capital letters
Lower turns to lowercase
Left withdraw left side N characters
Right withdraw right side N characters
Mid withdraw N characters of M position
Find determine the first position of the substring
Value change a string to a number
Access:
Excel:
Functions:
=sum( ) Calculate the sum Time: deduct time
=average( ) Calculate the average Convert to number
=max( ) Calculate the maximum Multiply with 24 to get day
switched to time
=min( ) Calculate the minimum
Divide w/ 365 to get years
=large( ) Largest N number
Divide w/ 30 to get months
=small( ) Smallest N number
=mode( ) Occurs most often
=median( ) Middle value
=count( ) Counts all the values with numbers in
=counta( ) Counts all the values
=countblank( ) Counts all the values that are blank
=power( ) To the power of e.g. 22
=rand( ) “random” number between 0 and 1
=randbetween( ) “random" number between a specific range of numbers
=round( ) rounds the number to the nearest whole number
=rounddown( ) round the number downwards e.g. Rounddown(4.567,2) = 4.56
=roundup( ) rounds the number upwards e.g. Roundup(4.567,2) = 4.57
=int( ) round a number down towards the nearest whole number
=trunc( ) removes the fraction of the number
=day( ) day between 1 and 31
=month( ) month between 1 and 12
=year( )
=days( ) the amount of days between 2 dates
=date( ) date
=today( ) todays date
=hour( ) hour between 1 and 23
=minute( ) minute between 1 and 59
=second( ) second between 1 and 59
=time( )
, =countif( ) only counts values according to specific criteria
=sumif( ) only counts numbers according to specific criteria
=countifs( ) only counts values according to specific criteria’s
=sumifs( ) only counts numbers according to specific criteria’s
Wildcard a placeholder that can showcase a character or a series of characters
* empty string – any number or character
? One exact character
*Note that we only use these at countif and sumif
=if( ) it allows you to make logical comparisons between a value and what you expect
=if( condition, true, false )
Nexted if if inside another if
And Multiple conditions – BOTH must be true
Not Invert criteria – true – false
Or One condition must be true
=vlookup( ) Vertical – top to bottom
=hlookup( ) Horizontal – left to right *remember absolute cell referencing
$ or F4
True approximate match
False exact match
Text functions:
Concatenate combine strings
Len find out how many characters the string has
Upper turns to capital letters
Lower turns to lowercase
Left withdraw left side N characters
Right withdraw right side N characters
Mid withdraw N characters of M position
Find determine the first position of the substring
Value change a string to a number
Access:
