34 Excel Formulas and Functions You Should Definitely Know
Thursday 05 December 2013

34 Excel Formulas and Functions You Should Definitely Know

 Here are 34 Numeric/Mathematical Functions, That You Should Definitely Know

By using functions, you can quickly and easily make many useful calculations, such as finding an average, the highest number, the lowest number, and a count of the number of items in a list. Microsoft Excel has many functions that you can use. Here describing 34 Numeric/Mathematical Functions only. String and Other Functions will be included in next Article.

Numeric/Mathematical Functions

1 ABS Function ABS function returns the absolute value of a number. (Remove '-' Symbol, If it have)
Syntax: ABS(number)Eg: If Column A1 is -10, Then ABS(A1) return 10.
ABS(-205) Returns 205. ABS(156) Return 156
2 AVERAGE Function AVERAGE Functionreturns the average (arithmetic mean) of the numbers provided.
Syntax: AVERAGE( number1, [number2], ... [number_n] ) or AVERAGE( rangeFrom:rangeTo)Eg: AVERAGE(12,15,13.2,8). AVERAGE(A1,A3,A5,A6,A7), AVERAGE(A1:A15)
3 AVERAGEA Function AVERAGEA Function acts like AVERAGE, it returns the average (arithmetic mean) of the numbers provided.
But, different from the Average function in that it treats TRUE as a value of 1 and FALSE as a value of 0.
4 AVERAGEIF Function AVERAGEIF Function returns the average (arithmetic mean) of all numbers in a range of cells, based on a given criteria.
Syntax: AVERAGEIF([condition_range], condition, [average_range] )Eg: AVERAGEIF(A2:A7, 2013, B2:B7) . It retrurn the average if B2 to B7 if its A column value is 2013)
AVERAGEIF(A2:A7, ">=2012", B2:B7)
5 CEILING Function CEILING Functionreturns a number rounded up based on a multiple of significance.
Syntax: CEILING( number, significance )Eg: if A1 is 210.67 then CEILING(A1, 1) is 211,
and CEILING(A1, 0.05) is 210.7, and CEILING(A1, 0.05) is 210.65
6 CONVERT Function CONVERT Function will convert a number from one measurement unit to another measurement unit.
Syntax: CONVERT( number, from_units, to_units )Eg: CONVERT(A1,"in","ft"), CONVERT(5,"F","C")
7 COUNT Function COUNT Functioncounts the number of cells that contain numbers as well as the number of arguments that contain numbers.(Count only numbers)
Syntax: COUNT( argument1, [argument2, ... argument_n] )Eg: COUNT(A1:A6), COUNT(A1:A6, 101)
8 COUNTA Function COUNTA Function counts the number of cells that are not empty as well as the number of arguments that contain values.(Count Any values)
Syntax is like as count function
9 COUNTBLANK Function COUNTBLANK Function counts the number of empty cells in a range.
Syntax: COUNTBLANK( range )Eg: COUNTBLANK(A1:A4)
10 COUNTIF Function COUNTIF Functioncounts the number of cells in a range, that meets a given criteria.
Syntax: COUNTIF( range, criteria )Eg: COUNTIF(A2:A7, 2000), COUNTIF(A2:A7, ">=2001")
11 EVEN Function EVEN Function rounds a number up to the nearest even integer. If the number is negative, the number is rounded away from zero.
Syntax: EVEN( number )Eg: if A1 is 210.61 then EVEN(A1) is 212
EVEN(-6121) is -6122
12 FACT Function FACT Functionreturns the factorial of a number.
Syntax: FACT( number )Eg: If A1 is 3 Then FACT(A1) is 6
13  FIX Function  FIX Function returns the integer portion of a number.
Syntax: Fix( expression )Eg: Fix(210.67) is 210
Fix(-2.98) is -2
14 FLOOR Function FLOOR Functionreturns a number rounded down based on a multiple of significance.
Syntax: FLOOR( number, significance )Eg: If A1 is 169.65 then FLOOR(A1, 1) is 169, FLOOR(A1, 0.05) is 169.65
15 FORMAT Function FORMAT Function takes an expression and returns it as a formatted string.
Syntax: Format ( expression, [ format ] )Eg: Format(210.6, "#,##0.00") is 210.60
Format(210.6, "Standard") is 210.60
Format(0.981, "Percent") is 98.10%, Format(1267.5, "Currency") is $1,267.50
16 INT Function INT Function returns the integer portion of a number.
Syntax: INT( expression )Eg: INT(210.67) is 210
17 LARGE Function LARGE Function returns the nth largest value from a set of values.
Syntax: LARGE( array, nth_position )Eg: LARGE(A1:A5, 1) will return the largest number from A1 to A5
LARGE(A1:A5, 2) will return the second largest number from A1 to A5
18 MAX Function MAX Function MAX function returns the largest value from the numbers provided.
Syntax: MAX( number1, [number2, ... number_n] )Eg: MAX(A2:A6), MAX(A3, A5, 45)
19 MIN Function MIN Function returns the smallest value from the numbers provided.
Syntax Like Max Function
20 MOD Function MOD Functionreturns the remainder after a number is divided by a divisor.
Syntax: MOD( number, divisor )Eg: MOD(A1, A2)
21 N Function N function converts a value to a number.
Syntax: N( value )Description: 
If value is a number, the N function returns a number.
If value is a date, the N function returns the date as a serial number.
If value is TRUE, the N function will return 1.
If value is FALSE, the N function will return 0.
If value is an error, the error value is returned.
For all other values, the N function will return 0.
22 ODD Function ODD Function rounds a number up to the nearest odd integer. If the number is negative, the number is rounded away from zero.
Syntax Like EVEN function
23 POWER Function POWER Function returns the result of a number raised to a given power.
Syntax: POWER( number, power )Eg: POWER(3, 4) is 81
24 PRODUCT Function PRODUCT Functionmultiplies the numbers and returns the product.
Syntax: PRODUCT( number1, number2, ... number_n )Eg: PRODUCT(3, 4) is 12, PRODUCT(3, 4, 5) is 60
25 ROMAN Function ROMAN Function converts a number to roman numeral.
Syntax: ROMAN( number, type )Eg: ROMAN(49) is XLIX
26 ROUND Function ROUND Function returns a number rounded to a specified number of digits
Syntax: ROUND( number, digits )
27 ROUNDDOWN Function  ROUNDDOWN Function  Â returns a number rounded down to a specified number of digits. 
28 ROUNDUP Function ROUNDUP Function returns a number rounded up to a specified number of digits.
29 SMALL Function SMALL Function returns the nth smallest value from a set of values.
Syntax Like LARGE Function
30 SQRT Function SQRT Function Â returns the square root of a number.
Syntax: SQRT( number )Eg: SQRT(25) is 5
31 SUM Function SUM Function adds all numbers in a range of cells and returns the result.
Syntax: SUM( number1, [number2, ... number_n] )Eg: SUM(A2, A3), SUM(A2:A6), SUM(A2:A3, A5:A6)
32 SUMIF Function SUMIF Function adds all numbers in a range of cells, based on a given criteria.
Syntax: SUMIF([criteria_range], criteria, [sum_range] )Eg: SUMIF(A2:A6, 2003, C2:C6) Returns the Sum of C2:C6 if it's A column value is Equal to 2003
SUMIF(A2:A6, ">=2001", C2:C6)
33 VAL Function VAL Function accepts a string as input and returns the numbers found in that string.
Syntax: Val( string )Eg: Val(" 34 10 Main Street") is 3410
Val(" 37 - 31 Main Street") is 3731
34  VALUE Function  VALUE Functionconverts a text value that represents a number to a number.
Syntax: VALUE( text )