EXCEL FORMULA

EXCEL FORMULA

What is Excel Formula ?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

THAT HAVE FOUR TYPE

1.DATE and Time Formula

2. Mathematical Formulas

3.Statistical Formula

4.Text Formulas

DATE and Time Formula

DATE() Formula

What this Formula does ?

This Formula returns a Valid Date in proper format by accepting 3 Numeric values as parameters as mentioned below in the Syntax. The best part of this formula is, It does not throw any error, even if you enter the Month Number as more than 12 (Month Can be only 12) or Day Number as more than 31. In such case it calculates the next valid date based on the input parameter.

Syntax:

=DATE(YEAR,MONTH,DAY)
Where:
YEAR : Numeric Value for the Year. It accepts Two Digit of the Year format or Complete Year.
MONTH : Numeric Value for the Month.
DAY : Numeric Value for the Day.

Example:

Day	Month	Year	Date Returned by Formula	Formula Used
10	11	95	November 10, 1995	=DATE(A1,B1,C1)
32	12	95	January 1, 1996	=DATE(A2,B2,C2)
30	13	95	January 30, 1996	=DATE(E6,D6,C6)

Let’s Discuss the above example for each row:

ROW 1 : It has all valid parameters. It has all Day, Month and Year parameters as Valid one. Based on these parameters DATE() Formula returned corresponding Date.

ROW 2: It has all valid parameters except the Day. In Day parameter 32 is passed. 32 can not be a date in any of the Month of any Year. Formula will not through any error. It will check the Month. Here month is 12 i.e. December. December has 31 days. So one day is extra from the last date of December. So the Formula will automatically consider this Date as 1st January. Here year is mentioned is 95. But now the date is adjusted in January, so the Year will also be shifted to 1996. This is the reason for the ROW 2, formula Returns January 1, 1996.

ROW 3: Here Month is passed as 13. But in a Year only 12 months are possible and 12th Month is December, so 13th Month is considered as January Month of the Next Year. Therefore the formula returns as January 30, 1996

DATEDIF() Formula

What this Formula does ?

Basically DATEDIF calculates difference between two dates. The most interesting part of this Function is that you can calculate the difference between two dates by a given intervals. If i say Intervals, what does it mean? Interval means, In what interval do you actually want the difference between two dates like total difference in Months, or Years or Days etc.

Syntax for the DATEDIF() Function:

=DATEDIF(StartDate, EndDate, Interval)

Where:
StartDate: is the First Date
EndDate: is the Second Date
Interval: This is the format or Type in which the difference you want

Note:

First Date should not be later than Second Date. If First Date is later than Second Date then the Formula will return an Error.

For Interval, we have few predefined Syntax, which you can use any one of them. Below is the list and Description for each of the Intervals

Interval	Meaning	Description
d	Day	Returns Total Number of Days between Two Dates
m	Month	Returns Total Number of Months between Two Dates
y	year	Returns Total Number of Years between Two Dates
yd	Days Excluding Years	Total Number of Days considering they are from the Same year.
ym	Months Excluding Years	Total Number of Months considering they are from the Same year.
md	Number of Days Excluding Years and Month	Total Number of Days considering they are from the Same Months and Same year.

Note:

1. If you are giving Dates and Interval Directly in your Formula then they both must be passed in DOUBLE QUOTES (“”) otherwise you can pass the reference directly.

=DATEDIF(StartDate,EndDate,”m”)

How to Calculate Age using this Function:

Using this Function we can calculate Age of Some One just by Passing his/her Birth date. In A1 Cell the Date of Birth is Kept. Considering that you can use the following Formula.

=DATEDIF(A1,TODAY(),”y”)&” Years “&DATEDIF(A1,TODAY(),”ym”)&” Months and “&DATEDIF(A1,TODAY(),”md”)&” Days”

DATEVALUE() Formula

What this Formula does ?

The DATEVALUE() Function Converts a Date, which is stored as Text in Excel, in to a Numeric Value or Serial Number, which Excel recognizes as a Date.
DATEVALUE function is helpful when you want to Filter or Sort some data based on Date Value which is stored as Text format in Excel.
Serial Number returned by this formula, can be seen as Date format by changing the Cell format as Date.

Syntax:

=DATEVALUE(date_text)
Where:
date_text : It is required. It is a Text in Excel Date format or a Cell reference which is having Text in Excel Date format. For Example 01-Jan-2012 or 01/01/12 etc. date_text range is from 01-01-1900 to 31-12-9999. If you provide any date out of this range, then this Formula will return #VALUE Error.

Remarks:

Excel stores all the dates in a sequence. It starts from 01-01-1900 and this date is stored as 1, 02-01-1900 as 2 and so on. Sequence Number of 01-Jan-2008 39448. After 39447 days from 01-Jan-1900, date will be 01-Jan-2008.

Example:

Date Text	Date Value	Formula used to get Date Value
25-DEC-99	36519	=DATEVALUE(A1)
25/12/99	36519	=DATEVALUE(A2)
10-01-99	36170	=DATEVALUE(A3)

DAY() Formula

What this Formula does ?

The DAY() Function returns the day of a Date or corresponding Serial Number.

Syntax:

=DAY(serial_number)
Where:
serial_number : It is required. Searial_Number is the date you want to find the Day. It can accept either a date or the corresponding date value, which you get from DATE VALUE Function.

Example:

Date	Day Returned by Formula	Formula used to get Day Value
25-DEC-99	25	=DAY(A1)
31/12/99	31	=DAY(A2)
10-01-99	10	=DAY(A3)

Remarks:

Normally the result will be a number, but this can be formatted to show the actual day of the week by using Format,Cells,Number,Custom and using the code ddd or dddd.


The DAY() function can be used to calculate the name of the day for your birthday.

DAYS360() Formula

What this Formula does ?

The DAYS360() Function Returns the the number of days between two days. It calculates based on 360 (30 X 12 = 360) days in a year.

Syntax:

DAYS360(start_date, end_date, [method])
Where:
start_date & end_date : These both arguement are required. These are dates between which you want the Number of Days. If start_date occurs after end_date, the DAYS360 function returns a negative number.
[method] : This is an optional Arguement. It specifies whether to use the U.S. or European method in the calculation. This is a Boolean Type arguement. If the Method is passed as TRUE, it means to use US calculation method and FALSE means to use European method in calculation.

What US and European Method of Calculation ?

US (Method – FALSE): If the starting date is the last day of a month, it becomes equal to the 30th day of the same month. If the ending date is the last day of a month and the starting date is earlier than the 30th day of a month, the ending date becomes equal to the 1st day of the next month; otherwise the ending date becomes equal to the 30th day of the same month.

European (Method – TRUE): Starting dates and ending dates that occur on the 31st day of a month become equal to the 30th day of the same month.

Example:

Start Date	End Date	Days between Two Days by Formula	Formula used to Calculate
01-Jan-98	05-Jan-98	4	=DAYS360(B1,C1,TRUE)
01-Jan-98	01-Feb-98	30	=DAYS360(B2,C2,TRUE)
01-Jan-98	31-Mar-98	89	=DAYS360(B3,C3,TRUE)

HOUR() Formula

What this Formula does ?

The HOUR() Function returns Hour of a given Time.

Syntax:

HOUR(serial_number)
Where:
Serial_Number: Is the time in number format. Time may be entered as Text strings within quotation marks (“5:30 AM”) or may be in Decimal format like 0.25 which represents 06:00:00 AM.
Result is always shown from 0 to 23.

Example:

Time Text	Hour	Formula used to get Hour
21:30	21	=HOUR(B1)
0.25	6	=HOUR(C1)

MINUTE() Formula

What this Formula does ?

The MINUTE() Function returns Minute of a given Time.

Syntax:

Minute(serial_number)
Where:
Serial_Number: Is the time in number format. Time may be entered as Text strings within quotation marks (“5:30 AM”) or may be in Decimal format like 0.25 which represents 06:00:00 AM.
Result is always shown from 0 to 59.

Example:

Time Text	Minute	Formula used to get Minute
17-07-2012 21:47	47	=MINUTE(B1)
21:15:00	15	=MINUTE(C1)
0.25	0	=MINUTE(D1)

MONTH() Formula

What this Formula does ?

The MONTH() Function returns Minute of a given Time.

Syntax:

MONTH(serial_number)
Where:
Serial_Number: is the date of the month you are trying to find. Date may be entered as Text strings like 01-Jan-1990 or may be the corresponding serial number of a valid date.

Example:

Original Date	Month Returned by the Formula	Formula used to get Month
01-Jan-98	1	=MONTH(B1)
01-Jan-98	January	=MONTH(C1)

Remarks:

Normally the result will be a number, but this can be formatted to show the actual month by using Format,Cells,Number,Custom and using the code mmm or mmmm.

NOW() Formula

What this Formula does ?

The NOW() Function shows the current date and time. The result will be updated each time the worksheet is opened and every time an entry is made anywhere on the worksheet. You can change the date and time format for the cell by using the commands in the Number group of the Home tab on the Ribbon.

Syntax:

NOW()
It does not have any arguement

Remarks:

The results of the NOW function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.

SECOND() Formula

What this Formula does ?

The SECOND() Function returns Minute of a given Time.

Syntax:

SECOND(serial_number)
Where:
Serial_Number: is the time of which Second you are trying to find. Time may be entered as Text strings like 6:00 AM or may be the corresponding decimal value of a valid time.

Example:

Original Time	Second Returned by the Formula	Formula used to get Second
4:48:18 PM	18	=SECOND(B1)
4:48 PM	0	=SECOND(C1)

Remarks:

Normally the result will be a number from 0 to 59.

TIME() Formula

What this Formula does ?

This Formula returns a Valid Time in proper format by accepting 3 Numeric values as parameters as mentioned below in the Syntax. The best part of this formula is, It does not throw any error, even if you enter the Second Number as more than 60 (Second Can be only 60) or Minute Number as more than 60. In such case it calculates the next valid time based on the input parameter.

Syntax:

=TIME(HOUR,MINUTE,SECOND)
Where:
HOUR : Numeric Value for the Hour.
MINUTE : Numeric Value for the Minute.
SECOND : Numeric Value for the Second.

Example:

Hour	Minute	Second	Time Returned by Formula	Formula Used to get Time
14	30	59	14:30:59	=TIME(A1,B1,C1)
14	62	59	3:02:59 PM	=TIME(A2,B2,C2)
14	30	63	14:31:03	=TIME(E6,D6,C6)

Let’s Discuss the above example for each row:

ROW 1 : It has all valid parameters. It has all Second, Minute and Hour parameters as Valid one. Based on these parameters TIME() Formula returned corresponding Time.

ROW 2: It has all valid parameters except the Minute. In Minute parameter 62 is passed. 62 can not be a Minute Value. Formula will not through any error. Minute is more than 60 here. Therefore 1 hour will be added in the hour and remaining minute will be kept. So the Formula will automatically consider this Time as 15:02:59.

ROW 3: Here Second is passed as 63. But for Second more than 60 is not possible. Therefore 1 minute will be added in Minutes and and remaining second will be there in Second Part. There the time will be 14:30:59

TIMEVALUE() Formula

What this Formula does ?

The TIMEVALUE() Function Converts a Time, which is stored as Text in Excel, in to a Numeric Value or Serial Number, which Excel recognizes as Time.
TIMEVALUE function is helpful when you want to Filter or Sort some data based on Time Value which is stored as Text format in Excel.
Serial Number returned by this formula, can be seen as Time format by changing the Cell format as Time.

Syntax:

=TIMEVALUE(time_text)
Where:
time_text : It is required. It is a Text in Excel Time format or a Cell reference which is having Text in Excel Time format. For Example 14:30:59.

Example:

Time Text	Time Value	Formula used to get Time Value
14:30:59	0.604849537	=TIMEVALUE(A1)
14:30:59	14:30:59	=TIMEVALUE(A2)
14:30:59	2:30:59 PM	=TIMEVALUE(A3)

TODAY() Formula

What this Formula does ?

The TODAY() Function shows the current date. The result will be updated each time the worksheet is opened and every time an entry is made anywhere on the worksheet. You can change the date format for the cell by using the commands in the Number group of the Home tab on the Ribbon.

Syntax:

TODAY()
It does not have any arguement

Remarks:

The results of the TODAY() function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.

WEEKDAY() Formula

What this Formula does ?

The WEEKDAY() Function shows the day of the week from a date.

Syntax:

=WEEKDAY(serial_number, [type])
Where:
serial_number : It is required. It is date for which you want to know the Day.
Type : It is an optional arguement. This is used to indicate the week day numbering system. There can be only following 3 values:
1 : will set Sunday as 1 through to Saturday as 7
2 : will set Monday as 1 through to Sunday as 7.
3 : will set Monday as 0 through to Sunday as 6.
Note : If you do not pass any value for this then the default value is considered as 1 by excel.

Example:

Date	Weekday Returned by Formula	Formula used to get Weekday
01-01-1998	5	=WEEKDAY(A1)
01-01-1998	5	=WEEKDAY(A2)
01-01-1998	5	=WEEKDAY(A3, 1)
01-01-1998	4	=WEEKDAY(A4 2)
01-01-1998	3	=WEEKDAY(A5 3)

YEAR() Formula

What this Formula does ?

The YEAR() Function returns Minute of a given Time.

Syntax:

YEAR(serial_number)
Where:
Serial_Number: is the date of the YEAR you are trying to find. Date may be entered as Text strings like 01-Jan-1990 or may be the corresponding serial number of a valid date.

Example:

Original Date	Year Returned by the Formula	Formula used to get Year
01-Jan-98	1998	=YEAR(B1)

Mathematical Formulas

ABS() Formula

What this Formula does ?

It is short form of ABSolute value. It means this function returns always a absolute value of a Number whether it is Negative or Positive.

Syntax:

=ABS(Number)
Where:
Number : Numeric Value for which you want to know the absolute value.

Example:

Number	Absolute Value (Returned by the Function)	Formula Used
10	10	=ABS(A1)
-32	32	=ABS(A2)
-10.5	10.5	=ABS(A3)

From the above Example, it is clear that this function accepts any positive or negative value, but it returns as Absolute value only (without + or – sign)

CEILING() Formula

What this Formula does ?

Basically CEILING returns the nearest multiple significance. The multiple significance is defined by user while writing this formula.

Syntax for the CEILING() Function:
=CEILING(number,significance)

Where:
Number: is the Number for which you want the nearest significance.
Significance: is the multiple to which you want to round off.

Remarks:

1. If any of the arguement is nonnumeric then formula will return #VALUE Error.

2. Sign of the number is not taken in to the consideration. It mean no matter whihc sign the number has, it will rounded off to the nearest multiple of significance. But for both the argument sign must be  same. Refer example row number – 6.

3. If Number and Significance have different signs then formula will return #NUM! Error. Refer below example row number – 5

Example:

Number	Rounded Value	Formula used to get Rounded Value
2.3	3	=CEILING(A1,1)
1.8	2	=CEILING(A2,1)
2.3	4	=CEILING(A3,2)
35	60	=CEILING(A4,30)
-40	#NUM!	=CEILING(A5,30)
-40	-60	=CEILING(A5,-30)

COMBIN() Formula

What this Formula does ?

The COMBIN() This function calculates the highest number of combinations available based upon a fixed number of items. The internal order of the combination does not matter, so AB is the same as BA.

Syntax:

=COMBIN(number, number_choosen)
Where:
number : This is the number which tell how many items are there to group.

number_choosen : This tells how many items can be in each group.

Example:

Total Number of Items	Total Number of Items in Each Group	Total Number of Possible Combinations	Formula used to get this Combination
4	2	6	=COMBIN(A1,B1)
4	3	4	=COMBIN(A2,B2)
10	2	45	=COMBIN(A3,B3)

Note: Let’s take an example. We have 4 letters A, B, C and D. We need to calculate the total number of possible combinations made with 2 letters.
Below are the total number of possible combinations:

1	AB
2	AC
3	AD
4	BC
5	BD
6	CD

With the above example, it is clear that Out of 4 letters, if we try to make different combinations of letters then a maximum of 6 combination is possible. (considering AB=BA, AC=CA and so on.)

Now lets go back and check the above formula, that also return 6 for the input 4 and 2. refer row number 1 in the above Example.

COUNTIF() Formula

What this Formula does ?

COUNTIF() is made with the combination of two keywords COUNT and IF. It means It COUNTs IF certain criteria is filfiled, which is provided by the user.

Syntax:

=COUNTIF(Range, Criteria)
Where:
Range : Range of items whihc you want to count. For example : Items in Range (A1:A30)
Criteria : This is the criteria, when matches then only item will be counted.

Example:

Data Table:

Date	Items	Price
01-Jul-12	Nike	200
10-Jul-12	Adidas	180
01-Jul-12	Nike	220
01-Jul-12	Liberty	100
20-Jul-12	Nike	150
10-Jul-12	Adidas	100
20-Jul-12	Liberty	150
01-Jul-12	Nike	150

Now based on above data table lets take some scenarios:

How many Nike Shoes Have been bought.	4	=COUNTIF(B1:B8,”Nike”)
How many Liberty Shoes been bought.	2	=COUNTIF(B1:B8,”Liberty”)
How many items cost £150 or above.	6	=COUNTIF(C1:C8,”>=150″)

Remarks:

The criteria can be typed in any of the following ways.

1. To match a specific number type the number, such as =COUNTIF(A1:A5,100)
2. To match a piece of text type the text in quotes, such as =COUNTIF(A1:A5,”Hello”)
3. To match using operators surround the expression with quotes, such as =COUNTIF(A1:A5,”>100″)

EVEN() Formula

What this Formula does ?

The EVEN() Function round a number up the nearest even whole number.

Syntax:

EVEN(Number)
Where:
Number : Is the Number which you want to round off till the nearest Even Whole Number.

Example:

Original Number	Rounded Value	Formula used to get Rounded Value
1	2	=EVEN(A1)
1.2	2	=EVEN(A2)
2.3	4	=EVEN(A3)
35	36	=EVEN(A4)

FACT() Formula

What this Formula does ?

The FACT() Function calculates the factorial of a given Number.

Syntax:

FACT(Number)
Where:
Number : Is the Number which you want to calculate the Factorial.

Example:

Number	Factorial of the Number	Formula used to Calculate Factorial
3	6	=FACT(A1)
3.5	6	=FACT(A2)
7	5040	=FACT(A3)
15	1307674368000	=FACT(A4)

Remarks:

Decimal points of the Number is ignored and Factorial is calculated on the Integer part only. Refer the above example Row 2.

FLOOR() Formula

What this Formula does ?

Basically FLOOR returns the nearest multiple significance number towards Zero. The multiple significance is defined by user while writing this formula.

Syntax:
FLOOR(number,significance)

Where:
Number: is the Number for which you want the nearest significance.
Significance: is the multiple to which you want to round off.

Remarks:

1. If any of the arguement is nonnumeric then formula will return #VALUE Error.

2. Sign of the number is not taken in to the consideration. It mean no matter whihc sign the number has, it will rounded off to the nearest multiple of significance. But for both the argument sign must be  same. Refer example row number – 6.

3. If Number and Significance have different signs then formula will return #NUM! Error. Refer below example row number – 5

Example:

Number	Rounded Value	Formula used to get Rounded Value
1.5	3	=FLOOR(A1,1)
2.3	2	=FLOOR(A2,1)
2.9	2	=FLOOR(A3,2)
199	150	=FLOOR(A4,50)
-40	#NUM!	=FLOOR(A5,30)
-190	-150	=FLOOR(A5,-50)

Difference between FLOOR() and CIELING():

Both the above functions do the nearest multiple of the significance. The difference is that CIELING() round to the Next multiple of the significance while FLOOR() to the Previous multiple of the significance.

INT() Formula

What this Formula does ?

Basically INT returns the nearest whole number.

Syntax:
INT(number)

Where:
Number: is the Number which you want to round off.

Number	Rounded Value	Formula used to get Rounded Value
1.5	1	=INT(A1,1)
2.3	2	=INT(A2,1)
2.9	2	=INT(A3,2)
-1.99	-2	=INT(A4,50)
-1.01	-2	=INT(A5,30)

MOD() Formula

What this Formula does ?

Basically MOD() returns the nearest whole number.

Syntax:
MOD(number,divisor)

Where:
Number: is the Number for which you want to find the remainder.
Divisor: is the divisor by which you want to divide the number.

Number	Divisor	Remainder	Formula used to get Remaindernbsp;
12	5	2	=MOD(A1,B1)
20	7	6	=MOD(A2,B2)
18	3	0	=MOD(A3,B3)
9	2	1	=MOD(A4,B4)

Remarks:

If divisor is 0, MOD returns the #DIV/0! error value.

ODD() Formula

What this Formula does ?

The ODD() Function round a number up the nearest ODD whole number.

Syntax:

ODD(Number)
Where:
Number : Is the Number which you want to round off till the nearest ODD Whole Number.

Example:

Original Number	Rounded Value	Formula used to get Rounded Value
2	3	=ODD(A1)
2.4	3	=ODD(A2)
2.9	3	=ODD(A3)
3.5	5	=ODD(A4)

PI() Formula

What this Formula does ?

The PI() Function is eqaul to the Value of Pi

Syntax:

PI()
No Arguement for this Function.

Example:

This can be used where ever you want to use the value of Pi. Like in calculation of Area of a Circle.

Radius of the Circle	Area of the Circle	Formula used to Calculate Area
5	78.54	=PI()*(A1^2)
10	314.16	=PI()*(C22^2)

POWER() Formula

What this Formula does ?

The POWER() Function raises a number to a user specified power. It is the same as using the ^ operator , such as 3^4, which result is 81. or POWER(3, 4) also returns 81.

Syntax:

POWER(Number, Power)
Where:
Number : Is the Number on which power is raised.
Power : Is the power number which is to be raised on the number.

Example:

Number	Power	Result	Formula used to Calculate Power
3	2	9	=POWER(A1, B1)
3	4	81	=POWER(A2, B2)
3	2	9	=A1^B1
3	4	81	=A2^B2

PRODUCT() Formula

What this Formula does ?

The PRODUCT() Function calculates the multiplication of a Range of Numbers.

Syntax:

PRODUCT(Number1, Number2, Number3, ….)

OR

PRODUCT(Range)
Where:
Number1, Number2,… : are the series of numbers for which you want to find the multiplication.
Range : Is the range of the numbers which you want the multiplication.

Example:

Number 1	Number 2	Multiplication	Formula used to Calculate Multiplication
3	2	6	=PRODUCT(A1, B1)
3	4	12	=PRODUCT(A2, B2)

RAND() Formula

What this Formula does ?

The RAND() Function always returns random number which is >=0 but <1.

Syntax:

RAND()

No Arguement required for this.

Example:

Using this RAND() function, we can generate random numbers in different ranges. For example :

Random greater than or equal to 0 but less than 1.	“Random Number”	=RAND()
Random greater than or equal to 0 but less than 10.	“Random Number”	=RAND() * 10
Random Number between 5 and 10..	“Random Number”	=RAND()*(10-5)+5

ROMAN() Formula

What this Formula does ?

The ROMAN() Function produces a number shown as Roman numerals in various formats.

Syntax:

ROMAN(Number, [form])

Where:
Number: is the number which you want to convert in Roman.
Form : is optional parameter. it has following values:

0 is Classic. This is used if no format is specified.
1 is more Concise.
2 is even more Concise.
3 is even more Concise still.
4 is Simplified.
TRUE is Classic
FALSE is Simplified

Example:

Number	Roman	Formula used for Roman
1	|	=ROMAN(A1)
2	II	=ROMAN(A2)
10	X	=ROMAN(A3)
1998	MCMXCVIII	=ROMAN(A4)
1998	MCMXCVIII	=ROMAN(A5, 0)
1998	MLMVLIII	=ROMAN(A6, 1)
1998	MXMVIII	=ROMAN(A7, 2)
1998	MVMIII	=ROMAN(A8, 3)
1998	MVMIII	=ROMAN(A9, 4)
1998	MCMXCVIII	=ROMAN(A10, TRUE)
1998	MVMIII	=ROMAN(A11, FALSE)

ROUND() Formula

What this Formula does ?

The ROUND() Function rounds a number to a specified amount of decimal places.

Syntax:

ROUND(Number, num_digits)

Where:
Number: is the number which you want to round off.
num_digits : is number of digit till which you want to round off after decimal.

Example:

Number	Places to Round	Rounded off Number	Formula used to Round off Number
1.47589	0	1	=ROUND(A1,B1)
1.47589	1	1.5	=ROUND(A2,B2)
1.47589	2	1.48	=ROUND(A3,B3)
13643.47589	-1	13640	=ROUND(A4,B4)
13643.47589	-2	13600	=ROUND(A5,B5)
13643.47589	-3	14000	=ROUND(A6,B6)

Remarks:

1. If 0 is used the number is rounded to the nearest whole number.
2. If a negative amount of rounding is used the figures to the left of the decimal point are rounded.

ROUNDDOWN() Formula

What this Formula does ?

The ROUNDDOWN() Function rounds a number down to a specified amount of decimal places.

Syntax:

ROUNDDOWN(Number, num_digits)

Where:
Number: is the number which you want to round off.
num_digits : is number of digit till which you want to round off after decimal.

Example:

Number	Places to Round	Rounded off Number	Formula used to Round off Number
1.47589	0	1	=ROUNDDOWN(A1,B1)
1.47589	1	1.4	=ROUNDDOWN(A2,B2)
1.47589	2	1.47	=ROUNDDOWN(A3,B3)
13643.47589	-1	13640	=ROUNDDOWN(A4,B4)
13643.47589	-2	13600	=ROUNDDOWN(A5,B5)
13643.47589	-3	13000	=ROUNDDOWN(A6,B6)

Remarks:

1. If 0 is used the number is rounded to the nearest whole number.
2. If a negative amount of rounding is used the figures to the left of the decimal point are rounded.

ROUNDUP() Formula

What this Formula does ?

The ROUNDUP() Function rounds a number up to a specified amount of decimal places.

Syntax:

ROUNDUP(Number, num_digits)

Where:
Number: is the number which you want to round off.
num_digits : is number of digit till which you want to round off after decimal.

Example:

Number	Places to Round	Rounded off Number	Formula used to Round off Number
1.47589	0	2	=ROUNDDOWN(A1,B1)
1.47589	1	1.5	=ROUNDDOWN(A2,B2)
1.47589	2	1.48	=ROUNDDOWN(A3,B3)
13643.47589	-1	13650	=ROUNDDOWN(A4,B4)
13643.47589	-2	13700	=ROUNDDOWN(A5,B5)
13643.47589	-3	14000	=ROUNDDOWN(A6,B6)

Remarks:

1. If 0 is used the number is rounded to the nearest whole number.
2. If a negative amount of rounding is used the figures to the left of the decimal point are rounded.

SIGN() Formula

What this Formula does ?

The SIGN() Function tests a value to determine whether it is positive or negative.

Note:

If the value is positive the result is 1.
If the value is negative the result is -1.
If the value is zero 0 the result is 0.

Syntax:

SIGN(Number)

Where:
Number: is the number for which you want to know the sign.

Example:

Number	Sign of the Number	Formula used to get Sign
10	1	=SIGN(A1)
20	1	=SIGN(A2)
0	0	=SIGN(A3)
-10	-1	=SIGN(A4)
-20	-1	=SIGN(A5)

SUM() Formula

What this Formula does ?

The SUM() Function calculates the Sum of a Range of Numbers.

Syntax:

SUM(Number1, Number2, Number3, ….)

OR

SUM(Range)
Where:
Number1, Number2,… : are the series of numbers for which you want to find the Sum.
Range : Is the range of the numbers which you want the Sum.

Example:

Number 1	Number 2	Sum of the Numbers	Formula used to Calculate Sum
3	2	5	=SUM(A1, B1)
3	4	7	=SUM(A2, B2)

SUMIF() Formula

What this Formula does ?

SUMIF() is made with the combination of two keywords SUM and IF. It means It SUMs IF certain criteria is filfiled, which is provided by the user.

Syntax:

=SUMIF(Range, Criteria, [sum_range])
Where:
Range : Range of items which you want to examine aginst the criteria. For example : Items in Range (A1:A30)
Criteria : This is the criteria, which will be matched with the Range.
sum_range : This is range which items will be summed up on matching the corresponding criteria. This is an optional parameter. If you want to sum the items of same range where you are applying the criteria, then you can ignore this. Refer example row No-3.

Example:

Data Table:

Date	Items	Price
01-Jul-12	Nike	200
10-Jul-12	Adidas	180
01-Jul-12	Nike	220
01-Jul-12	Liberty	100
20-Jul-12	Nike	150
10-Jul-12	Adidas	100
20-Jul-12	Liberty	150
01-Jul-12	Nike	150

Now based on above data table lets take some scenarios:

Total Price of Nike Shoes.	720	=SUMIF(B1:B8,”Nike”,C1:C8)
Total Cost of Liberty Shoes bought	250	=SUMIF(B1:B8,”Liberty”,C1:C8)
Total cost of Items costing £150 or above.	1050	=SUMIF(C1:C8,”>=150″)

SUMPRODUCT() Formula

What this Formula does ?

This function uses at least two columns of values. The values in the first column are multipled with the corresponding value in the second column.
The Sum of all the values is the result of the calculation.

Syntax:

=SUMPRODUCT(Array1, Array2, Array3, ….)
Where:
Array1, Array2, … : are the Column Range which you want to multiply with and get the Total of all the multiplies.

Example:

Data Table:

Brands	Quantity Sold	Price of Each Quantity
Nike	10	200
Liberty	7	180
Adidas	9	150

Now based on above data table lets take some scenarios:

Total Sales Value.

4610

=SUMPRODUCT(B2:B4,C2:C4)

How It Works:

Let’s take the Above Example. First It will take value from both the columns and start multiplying them for each row.
PRODUCT of ROW 1 : 10 * 200 = 2000
PRODUCT of ROW 2 : 7 * 180 = 1260
PRODUCT of ROW 3 : 9 * 150 = 1350

Now It will Total all the products of each Row: PRODUCT of ROW 1 + PRODUCT of ROW 2 + PRODUCT of ROW 3
Therefore the result is : 2000 + 1260 + 1350 = 4610

Note : For this minimum 2 column is required.

TRUNC() Formula

What this Formula does ?

The TRUNC() Function truncates the decimal part of a number. It does not actually round the number.

Syntax:

TRUNC(Number, num_digits)

Where:
Number: is the number which you want to round off.
num_digits : is number of digit till which you want to truncate after decimal.

Example:

Number	Places to Truncate	Truncated Number	Formula used to Truncate the Number
1.47589	0	1	=TRUNC(A1,B1)
1.47589	1	1.4	=TRUNC(A2,B2)
1.47589	2	1.47	=TRUNC(A3,B3)
-1.47589	2	-1.47	=TRUNC(A3,B3)
13643.47589	-1	13640	=TRUNC(A4,B4)
13643.47589	-2	13600	=TRUNC(A5,B5)
13643.47589	-3	13000	=TRUNC(A6,B6)

Statistical Formula      

   

AVERAGE	CORREL
COUNT	COUNTA
FORECAST	FREQUENCY
GROWTH	LARGE
MAX	MEDIAN
MIN	TIMEVALUE
MODE	PERMUT
QUARTILE	RANK
SMALL	STDEV
STDEVP	TREND
VAR	VARP

Text Formulas
CHAR	CLEAN
CODE	CONCATENATE
DOLLAR	EXACT
FIND	FIXED
LEFT	LEN
LOWER	MID
PROPER	REPLACE
REPT	RIGHT
SUBSTITUTE	T
TEXT	TRIM
UPPER	VALUE