Name Sintax Example (in function2007.

xlsm)
STATISTICAL
AVERAGE(n1;n2;...)
Calculates the average (arithmetic mean) of the values in the list of arguments.
Average Empty or nom numeric values are ignored. Freq1 average AVERAGE(D2:D11)

AVERAGEA(v1;v2;...)
Calculates the average (arithmetic mean) of the values in the list of arguments. Freq1 average, in this case empty and
AverageA Empty or nom numeric values are used as 0. nom numeric cells count as 0 AVERAGEA(D2:D11)

AVERAGEIF(range;criteria;avg_range)
Returns the average (arithmetic mean) of all the cells in avg_range that meet a given
criteria in range Final grade average for students with
AverageIF If you omit avg_range then it will return the average of values in range freq1 >=10 AVERAGEIF(D2:D11,">=10",F2:F11)

AVERAGEIFS(avg_range;range1;criteria1;range2;criteria2;…) Final grade average for students with both AVERAGEIFS(F2:F11,D2:D11,">=10",E2

AverageIFS Returns the average (arithmetic mean) of all cells that meet multiple criteria. freq1 and freq2 >=10 :E11,">=10")

CORREL(array1;array2)
Returns the correlation coefficient of the array1 and array2 cell ranges. Correlation coefficient of freq1 and freq2
Correl Use the correlation coefficient to determine the relationship between two properties. grades CORREL(D2:D11,E2:E11)
COUNT(v1;v2;...)
Counts the number of values v1, v2... that represent numeric values
v1;v2;... are values or ranges. Number of students with a numeric value
Count If a value cannot be converted to numeric will be ignored in freq1 COUNT(D2:D11)

COUNTA(v1;v2;...)
Counts the number of nom empty cells
v1;v2;... Are values or ranges
CountA Only empty cells will be ignored Number of nom empty cells in freq1 COUNTA(D2:D11)

COUNTBLANK(range)
CountBlank Counts the number of empty cell in range Number of empty cells in freq1 COUNTBLANK(D2:D11)

COUNTIF(range;criteria)
CountIf Counts the number of cells within range that meet a single criteria that you specify Number of students with freq1 >=10 COUNTIF(D2:D11,">=10")

COUNTIFS(range1;criteria1;range2;criteria2;...)
Applies criteria to cells across multiple ranges and counts the number of times all Number of students with both freq1 and COUNTIFS(D2:D11,">=10",E2:E11,">=1
CountIfs criteria are met. freq2 >=10 0")

COVAR(array1;array2)
Returns covariance, the average of the products of deviations for each data point pair. Covariance between freq1 and freq2
CoVar Use covariance to determine the relationship between two data sets array1, array2 grades COVAR(D2:D11,E2:E11)

FREQUENCY(data_array;bins_array) How many freq2 grades fall in the

Calculates how often values occur within a range of values, and then returns a vertical intervals:
Frequency array of numbers. <=9;10-14;15-16;>16 FREQUENCY(E2:E11,I15:I17)

LARGE(array;k)
Large Returns the k-th largest value in array 3ª better grade in freq2 LARGE(E2:E11,3)

MAX(n1;n2;...)
Returns the largest value in a set of values or in a range
Max Nom numeric values are ignored Best freq1 grade MAX(D2:D11)
MAXA(v1;v2;...)
Returns the largest value in a set of values or in a range
Text that can be converted to numeric and the logic values (True, False) will also be
considered. Best freq1 grade. FALSE will be used as
MaxA False evaluate as 0, True evaluates as 1. 0 MAXA(D2:D11)

MEDIAN(n1;n2;...)
Returns the median of the given numbers.
Median The median is the number in the middle of a set of numbers Median of freq2 grades MEDIAN(E2:E11)

MIN(n1;n2;...)
Returns the smallest value in a set of values or in a range
Min Nom numeric values are ignored Worst freq1 grade MIN(D2:D11)
MINA(v1;v2;...)
Returns the smallest value in a set of values or in a range
Text that can be converted to numeric and the logic values (True, False) will also be
considered. Worst freq1 grade. FALSE will be used as
MinA False evaluate as 0, True evaluates as 1. 0 MINA(D2:D11)

MODE(n1;n2;...)
Mode Returns the most frequently occurring, or repetitive, value in an array or range of data Freq2 mode MODE(E2:E11)
RANK(number;ref;order)
Returns the rank of a number in a list of numbers.
The rank of a number is its size relative to other values in a list. (ref)
If order is 0 or omitted number 1 in rank will be the largest value in ref
Rank If order is 1 number 1 in rank will be the smallest value in ref Rank of 17 in freq1 RANK(17,D2:D11,0)

SMALL(array;k)
Small Returns the k-th smallest value in a data set or range 2ª worst grade in freq2 SMALL(E2:E11,2)
STDEV(n1;n2;...)
Estimates standard deviation based on a sample.
The standard deviation is a measure of how widely values are dispersed from the
StDev average value (the mean). Freq2 standard deviation STDEV(E2:E11)
VAR(v1;v2;...)
Estimates variance based on a sample
Var v1;v2;...are values or ranges Freq2 variance VAR(E2:E11)
 VARP(v1;v2;...)
Calculates variance based on the entire population
Varp v1;v2;...are values or ranges Freq2 variance VARP(E2:E11)

LOGICAL
IF(logical_test;value_if_true;value_if_false)
The IF function returns value_if_true if a logical_test evaluates to TRUE, and Write "APROVADO" if D3 grade >=10 IF(D3>=10,"APROVADO","REPROVADO
If value_if_false if that condition evaluates to FALSE othewise write "REPROVADO" ")
AND(logical1;logical2;...)
Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more
And arguments evaluate to FALSE. See if D3 and E3 grades are both >=10 AND(D3>=10,E3>=10)

OR(logical1;logical2;...) See if, at least, one of D3, E3 grades is

Or Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE. >=10 OR(D3>=10,E3>=10)

INFORMATION
IFERROR(value;value_if_error)
Returns value_if_error if a formula evaluates to an error; otherwise, returns the result of Return the D2/D9 value or the word
Iferror the formula. "ERRO" if division fails IFERROR(D2/D9,"ERRO")

ISBLANK(value)
IsBlank Returns true if value refers to an empty cell. Check if D9 cell isempty ISBLANK(D9)
ISERROR(value)
Returns true if value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!,
IsError #NUM!, #NAME?, or #NULL!). Check if there is an error in cell F10 ISERROR(F10)

ISNUMBER(value) Check if the value in cell D10 can be

IsNumber Returns true if value refers to a number converted to numeric ISNUMBER(D10)
ISTEXT(value)
IsText Returns true if value refers to text Check if cell D10 contains text ISTEXT(D10)

LOOKUP AND REFERENCE

LOOKUP(lookup_value; lookup_vector; result_vector)
Look in a one-row or one-column range (lookup_vector) for lookup_value and return a
value from the same position in a second one-row or one-column range (result_vector)
lookup_vector must be placed in ascendingorder. If the LOOKUP function can't find the
lookup_value, the function matches the largest value in lookup_vector that is less than
LookUp or equal to lookup_value. Name of student with 150105127 number LOOKUP(150105127,B2:B11,C2:C11)
HLOOKUP(lookup_value;table_array;row_index_num;range_lookup)
Searches for lookup_value in the top row of table_array, and then returns a value in the
same column from index_num row you specify in table_array.
If range_lookup is TRUE, the values in the first row of table_array must be placed in
HLookUp ascending order. Freq2 weight HLOOKUP("freq2",I1:J2,2,FALSE)
VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)
Searches for lookup_value in the first column of table_array, and then returns a value in
the same row from index_num column you specify in table_array.
If range_lookup is TRUE, the values in the first column of table_array must be placed in
VLookUp ascending order. Freq2 grade for Rita Lee VLOOKUP("Rita Lee",C2:F11,3,FALSE)

INDEX(array;row_num;col_num)
Returns the value of an element in array, selected by the row_num and col_num
Index number indexes. Row 7, column 2 value of B2:F11 range INDEX(B2:F11,7,2)

INDIRECT(ref_text;a1)
Returns the reference specified by ref_text text string. References are immediately
evaluated to display their contents.
If a1 is true or omitted ref_text is expected in the A1 style otherwise is expected in the
Indirect R1C1 style.
( p_ ; p_ y; _ yp ) Value of cell referenced by I4 value. INDIRECT(I4)
Searches for lookup_value in lookup_array, and then returns the relative position of that
item in the range.
If match_type is 1 or omitted lookup_array must be placed in ascending order.
If match_type is -1 lookup_array must be placed in descending order. Position of "Nuno Martins" in C2:C11
Match If match_type is 0 lookup_array can be in any order. range MATCH("Nuno Martins",C2:C11,0)

OFFSET(ref; lin; cols;alt;larg)

Returns a reference to a range that is a specified lin of rows and cols columns from ref. Value of cell, 2 rows down and one cell
Offset alt and larg will be the number of rows and columns of the new range. right from B5 OFFSET(B5,-2,1,1,1)

ROWS(array)
Rows Returns the number of rows in array. Number of rows in B2:F11 range ROWS(B2:F11)

COLUMNS(array)
Columns Returns the number of columns in array. Number of columns in B2:F11 range COLUMNS(B2:F11)

MATH
CEILING(num1;num2)
Ceiling Returns the first multiple of num2 greater or equal to num1 First multiple of 7 greater or equal to 29 CEILING(29,7)

DEGREES(angle)
Degrees Returns the corresponding degrees of angle radians PI radians to degrees DEGREES(PI())

FACT(number)
Fact Returns the factorial of number. Factorial of 7 FACT(7)

FLOOR(num1;num2)
Floor Returns the first multiple of num2 smaller or equal to num1. First multiple of 7 smaller or equal to 29 FLOOR(29,7)
 INT(number)
Returns the first integer less or equal to number.
Int(8.9) returns 8
Int Int(-8.9) returns -9 First integer number smaller than 8.9 INT(8.9)

LCM(num1;num2;...;numk)
Returns the least common multiple of integers.
The least common multiple is the smallest positive integer that is a multiple of all integer
Lcm arguments Least common multiple of 27 and 33 LCM(27,33)

MDETERM(array)
Mdeterm Returns the matrix determinant of array Matrix Determinant of D7:E8 MDETERM(D7:E8)

MINVERSE(array)
Minverse Returns the matrix inverse of array Matrix inverse of D7:E8 MINVERSE(D7:E8)
MMULT(array1;array2)
Returns the matrix product of array1 and array2 Matrix product of D7:E8 matrix by F7:F8
Mmult Return matrix will have array1 number of rows and array2 number of columns. matrix MMULT(D7:E8,F7:F8)

MOD(number;divisor)
Returns the remainder from division
Mod The same as - divisor * INT( number / divisor ) Remainder after division of 15 by 4 MOD(15,4)
MROUND(num1;num2)
Mround Returns the multiple of num2 closer to num1. Multiple of 5 closer to 8 MROUND(8,5)
PI()
Pi Returns the value of PI with 15 digit precision. Value of Pi PI()
POWER(number;pow)
Returns number to the power of pow
Power The same as number^pow 5 to the power of 3 POWER(5,3)

RADIANS(angle)
Radians Returns the corresponding radians of angle degrees. 90 degrees converted to radians RADIANS(90)

RAND()
Returns an evenly distributed random real number greater than or equal to 0 and less
than 1. Real number greater than or equal to 0
Rand A new random real number is returned every time the worksheet is calculated. and less than 1 RAND()

RANDBETWEEN(lim_inf;lim_sup) Integer random number between 10 and

Randbetween Returns a random integer number between lim_inf and lim_sup 20 RANDBETWEEN(10,20)

ROUND(number;num_digits)
Round Rounds number to a specified num_digits digits Round 15.276 to 2 decimal digits ROUND(15.276,2)
SUM(n1;n2;...)
The SUM function adds all the numbers that you specify as arguments.
Each argument can be a range, a cell reference, an array, a constant, a formula, or the
result from another function
Sum Empty or nom numeric cells will be ignored Sum of values in D2:D11 range SUM(D2:D11)

SUMIF(range;criteria;sum_range)
You use the SUMIF function to sum the values in sum_range that meet criteria in range Sum of final grade for students with grade
Sumif If you omit sum_range the values in range will be summed <10 in Freq1 SUMIF(D2:D11,"<10",F2:F11)

SUMIFS(sum_range;range1;criteria1;range2;criteria2…) Sum of final grade for students with both SUMIFS(F2:F11,D2:D11,"<10",E2:E11,"<

Sumifs Adds the cells in sum_range that meet multiple criteria. Freq1 and Freq2 <10 10")
SUMPRODUCT(array1;array2;array3...)
Returns the sum of the products of corresponding array components (array1, array2,
Sumproduct array3 ...) Sum of products of D2:D11 and E2:E11 SUMPRODUCT(D2:D11,E2:E11)

SQRT(number)
Sqrt Square root of number Square root of 16 SQRT(16)

TRANSPOSE(array)
Transpose Transpose matrix of array Transpose matrix of D7:E8 matrix TRANSPOSE(D7:E8)
TRUNC(number)
Returns the integer part of number
For positive numbers INT e TRUNC return the same
INT(-7.8) = -8
Trunc TRUNC(-7.8) = -7 Integer part of -7.8 TRUNC(-7.8)

TEXT
CHAR(code)
Char Returns the character specified by code Character with ASCII code 65 CHAR(65)

CODE(text)
Code Returns a numeric (ASCII) code for the first character in text. Code for letter A CODE("A")
FIND(find_text; within_text; start_num)
Locates find_text string within within_text string, and return the number of the starting
position of the first text string from the first character of the second text string.
If you omit start_num search starts in the first character of within_text Find the position of "TÓ" in the word
Find If find_text is not found FIND will return a error. (FIND is case sensitive) "CATÓLICA" FIND("TÓ", "CATÓLICA")

LEFT(text; num_chars)
Left Returns the leftmost num_chars characters from text. First 3 characters of "CATÓLICA" LEFT("CATÓLICA", 3)

LEN(text) How many characters in the word

Len Number of characters in text "CATÓLICA"? LEN("CATÓLICA")
LOWER(text)
Lower Converts text to lowercase Lowercase of "CATÓLICA" LOWER("CATÓLICA")

MID(text; start_num; num_chars) 2 characters starting at position 3 in the

Mid Returns num_chars characters from text, starting at the start_num position you specify. word "CATÓLICA" MID("CATÓLICA",3,2)
 PROPER(text) The text "pedro rolinho silva" with first
Proper Capitalizes the first letter in each word of text. letter of each word in uppercase PROPER("pedro rolinho silva")

RIGHT(text; num_chars)
Right Returns the rightmost num_chars characters from text. Last 3 characters of "CATÓLICA" RIGHT("CATÓLICA",3)

SEARCH(find_text; within_text; start_num)

The same as FIND but SEARCH is not case sensitive and you can use wild characters Find the position of "tó" in the word
Search like "*" and "?" "CATÓLICA" SEARCH("tó", "CATÓLICA")

SUBSTITUTE(text; old_text; new_text; instance_num)

Substitutes old_text for new_text in a text string
Instance_num specifies which occurrence of old_text you want to replace with
new_text. If you specify instance_num, only that instance of old_text is replaced.
Substitute Otherwise, every occurrence of old_text in text is changed to new_text. Replace all "A" in "CATÓLICA"by "B" SUBSTITUTE("CATÒLICA", "A", "B")

TEXT(value;text_format)
Converts a numeric value to text and lets you specify the display formatting by using
text_format. Todays date (in J4) in "dd-mmm-aaaa"
Text =text(10;"00000") -- » 00010 format TEXT(J4,"dd-mmm-aaaa")

TRIM(text)
Trim Removes all spaces in the beginning and in the end of text Remove spaces from " CATÓLICA " TRIM(" CATÓLICA ")

UPPER(text)
Upper Converts text to uppercase Uppercase of "católica" UPPER("católica")

DATE AND TIME

DATE(year; month; day) Date corresponding to year 2008, month
Date Returns the sequential serial number that represents a particular date. 5, day 23 DATE(2008,5,23)

DATEDIF(ini_date;end_date;uni_temp)
Returns distance from ini_date to end_date measured in uni_temp units DATEDIF(DATE(1993,07,13),TODAY(),"
Datedif If uni_temp "Y" - returns years, "M" - returns months, "D" - returns days Age of someone born in 13-07-1993 Y")

DAY(serial_number)
Day Returns a 1-31 integer number representing the day corresponding to serial_number. Current system day ( in J4) DAY(J4)

MONTH(serial_number)
Month Returns a 1-12 integer number representing the month corresponding to serial_number. Current system month ( in J4) MONTH(J4)

YEAR(serial_number)
Year Returns an integer number representing the year corresponding to serial_number. Current system year (in J4) YEAR(J4)
WEEKDAY(serial_number; return_type)
Returns the day of the week corresponding to a date. The day is given as an integer,
ranging from 1 (Sunday) to 7 (Saturday), by default.
If return_type 1 or omitted Numbers 1 (Sunday) through 7 (Saturday).
2 - Numbers 1 (Sunday) through 7 (Saturday). Day of the week corresponding to system
Weekday 3 - Numbers 0 (Monday) through 6 (Sunday). date (in J4) WEEKDAY(J4)
NOW()
Now Returns system date and time. System date and Time Now()
TODAY()
Returns system date.
Today Devolve a data do sistema. System Date Today()

FINANCIAL
FV(rate; nper; pmt; pv ; type)
Returns the future value of an investment based on periodic, constant payments and a
constant interest rate.
nper is the total number of payment periods in an annuity, pmt is the payment made
each period; it cannot change over the life of the annuity. If pmt is omitted, you must
include the pv argument. pv is the present value, or the lump-sum amount that a
series of future payments is worth right now. If pv is omitted, it is assumed to be 0 Future Value of 1000 euros put in the
(zero), and you must include the pmt argument. type 0 or omitted payments in the end bank, during 10 years with 3% interest
FV of period, 1 in the beginning of period rate FV(3%,10,-1000,0,0)
PMT(rate; nper; pv; fv; type)
Calculates the payment for a loan based on constant payments and a constant interest
rate.
rate is the interest rate for the loan. nper is the total number of payments for the loan.
pv is the present value, or the total amount that a series of future payments is worth
now; also known as the principal. fv is the future value, or a cash balance you want to
attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that
is, the future value of a loan is 0. type 0 or omitted payments in the end of period, 1 in Anual payment for a 10000 euros loan,
PMT the beginning of period during 10 years and a 5% interest rate PMT(5%,10,10000,0)
PV(rate; nper; pmt; fv; type) Present value of a loan, to be payed in 10
Returns the present value of an investment. The present value is the total amount that years, 5% interest rate and 1295,05 euros
PV a series of future payments is worth now. anual payments PV(5%,10,-1295.05,0)