Functions Of MS-Excel

Ms-Word

Date and Time Functions	Logical Functions	Math Functions	Statical Functions
Text Functions	Test Questions	Try Functions

Date and Time functions

1. DATE Returns the serial number of a particular date

Syntax

DATE(year,month,day)

=date(2006,5,10)

2. DATEVALUE Converts a date in the form of text to a serial number

=DATEVALUE("8/22/2008")

Serial number of the text date, using the 1900 date system (39682)

3. DAY Converts a serial number to a day of the month

A
Date
15-Apr-2008
Formula	Description (Result)
=DAY(A2)	Day of the date above (15)

4. DAYS360 Calculates the number of days between two dates based on a 360-day year

A
Dates
1/30/2008
2/1/2008
Formula	Description (Result)
=DAYS360(A2,A3)	Number of days between the two dates above, based on a 360-day year (1)

5. HOUR Converts a serial number to an hour

	A
1	3:30:30 AM	=HOUR(A2)	Hour of first time (3)

6. MINUTE Converts a serial number to a minute

A
Time
4:48:00 PM
Formula	Description (Result)
=MINUTE(A2)	Minutes of the time above (48)

7. MONTH Converts a serial number to a month

	A
1	6-Apr-2005	=month(A1)=> Month of the date above (4)

8. NOW Returns the serial number of the current date and time

=now()

9. SECOND Converts a serial number to a second

=second(4:48:18 PM)

=>it returns Seconds in the first time (18)

10. TIME Returns the serial number of a particular time

Syntax

TIME(hour,minute,second)

11. TIMEVALUE Converts a time in the form of text to a serial number

=TIMEVALUE("2:24 AM")

Decimal part of a day, for the time (0.1)

12. TODAY Returns the serial number of today's date

=today()

13. WEEKDAY Converts a serial number to a day of the week

=WEEKDAY(7/14/2008)

14. YEAR Converts a serial number to a year

=year(date)

Logical functions

(( Top ))

1. AND Returns TRUE if all its arguments are TRUE

=IF(AND(1<104, 104<100), 104, "The value is out of range.")

Displays the second number above, if it is between 1 and 100, otherwise displays a message (The value is out of range.)

2. IF Specifies a logical test to perform

Syntax

IF(logical_test,value_if_true,value_if_false)

	A	B
1	45	50
	=IF(A1>B1,"Over Budget","OK")

Result is OK

	A
1	100

=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F"))))

3. NOT Reverses the logic of its argument

4. OR Returns TRUE if any argument is TRUE

5. TRUE Returns the logical value TRUE

Some Important

Math Functions

(( Top ))

1. ABS Returns the absolute value of a number.

=abs(-20) returns 20

2. CEILING Rounds a number to the nearest integer or to the nearest multiple of significance

=CEILING(2.5, 1)	Rounds 2.5 up to nearest multiple of 1 (3)
=CEILING(-2.5, -2)	Rounds -2.5 up to nearest multiple of -2 (-4)
=CEILING(-2.5, 2)	Returns an error, because -2.5 and 2 have different signs (#NUM!)
=CEILING(1.5, 0.1)	Rounds 1.5 up to the nearest multiple of 0.1 (1.5)
=CEILING(0.234, 0.01)	Rounds 0.234 up to the nearest multiple of 0.01 (0.24)

3. COUNTIF Counts the number of nonblank cells within a range that meet the given criteria

COUNTIF(range,criteria)

Range is the range of cells from which you want to count cells.

        A           B
1        Data       Data
2        apples     32
3        oranges    54
4        peaches    75
5        apples     86

=COUNTIF(A2:A5,"apples")

Number of cells with apples in the first column above (2)

4. FACT Returns the factorial of a number

=FACT(5) RETURNS 120 (5*4*3*2*1)

5. FLOOR Rounds a number down, toward zero

=FLOOR(2.53 ,1) RETURNS 2

6. GCD Returns the greatest common divisor

=GCD(5, 2)

Greatest common divisor of 5 and 2 (1)

7. INT Rounds a number down to the nearest integer

=INT(8.9)

Rounds 8.9 down (8)

8. LCM Returns the least common multiple

=LCM(5, 2)

Least common multiple of 5 and 2 (10)

9. MOD Returns the remainder from division

=MOD(3, 2)

Remainder of 3/2 (1)

10. POWER Returns the result of a number raised to a power

=POWER(5,2)

5 squared (25)

11. PRODUCT Multiplies its arguments

=PRODUCT(10,20,30) RETURNS 6000

=PRODUCT(RANGE)

12. ROMAN Converts an arabic numeral to roman, as text

=ROMAN(499,0)	Classic roman numeral style for 499 (CDXCIX)
=ROMAN(499,1)	More concise version for 499 (LDVLIV)
=ROMAN(499,2)	More concise version for 499 (XDIX)
=ROMAN(499,3)	More concise version for 499 (VDIV)
=ROMAN(499,4)	More concise version for 499 (ID)
=ROMAN(2013,0)	Classic roman numeral style for 2013 (MMXIII)

13. ROUND Rounds a number to a specified number of digits

=ROUND(2.149, 1)	Rounds 2.149 to one decimal place (2.1)
=ROUND(-1.475, 2)	Rounds -1.475 to two decimal places (-1.48)

14. ROUNDDOWN Rounds a number down, toward zero

=ROUNDDOWN(3.2, 0)	Rounds 3.2 down to zero decimal places (3)
=ROUNDDOWN(76.9,0)	Rounds 76.9 down to zero decimal places (76)

15. ROUNDUP Rounds a number up, away from zero

=ROUNDUP(3.2,0)	Rounds 3.2 up to zero decimal places (4)
=ROUNDUP(76.9,0)	Rounds 76.9 up to zero decimal places (77)

16. SQRT Returns a positive square root

=SQRT(16)

Square root of 16 (4)

17. SUBTOTAL Returns a subtotal in a list or database

Function_num is the number 1 to 11 that specifies which function to use in calculating subtotals within a list.

Function_Num Function

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

A B C D E
1 10 10 10 10 10

=SUBTOTAL(9,A1:E1) RETURNS 50

18. SUM Adds its arguments

=SUM(10,20,30) RETURNS 60

A B C D

1 10 20 35 35

=SUM(A1:D1) RETURNS 100

19. SUMIF Adds the cells specified by a given criteria

A B C D

1 10 20 35 35

=SUMIF(A1:D1,”>20”) RETURNS 70

20. SUMPRODUCT Returns the sum of the products of corresponding array components

____________________________________________________________________
A B C D
____________________________________________________________________
1 Array 1 Array 1 Array 2 Array 2
2 3 4 2 7
3 8 6 6 7
4 1 9 5 3

Formula	Description (Result)
=SUMPRODUCT(A2:B4, C2:D4)	Multiplies all the components of the two arrays and then adds the products — that is, 32 + 47 + 86 + 67 + 15 + 93. (156)

Some important

Statistical functions

(( Top ))

1. AVERAGE Returns the average of its arguments

A B C D

1 12 45 56 67

=AVERAGE(A1:D1) RETURNS 45

2. COUNT Counts how many numbers are in the list of arguments

A B C D E F G H

1 12 Ram 56 67 apple 12

=count(a1:h1) returns 4

3. COUNTA Counts how many values are in the list of arguments

A B C D E F G H

1 12 Ram 56 67 apple 12

=counta(a1:h1) returns 6 (it returns the no. of non empty cells.)

4. LARGE Returns the k-th largest value in a data set

A B C D E F G H

1 25 56 76 89 45 72 56 70

=LARGE(A1:H1,1) RETURNS 89 ( THE LARGEST NO. OF LIST )

5. MAX Returns the maximum value in a list of arguments

A B C D E F G H

1 12 45 65 75 65 54 33 79

=MAX(A1:H1) RETURNS 79

6. MIN Returns the minimum value in a list of arguments

A B C D E F G H

1 12 45 65 75 65 54 33 79

=MIN(A1:H1) RETURNS 12

7. SMALL Returns the k-th smallest value in a data set

A B C D E F G H

1 25 56 76 89 45 72 56 70

=SMALL(A1:H1,1) RETURNS 25 ( THE LARGEST NO. OF LIST )

=SMALL(A1:H1,2) RETURNS 45 ( THE LARGEST NO. OF LIST )

Text and Data functions

(( Top ))

1. CHAR Returns the character specified by the code number

=CHAR(65) RETURNS A

2. CONCATENATE Joins several text items into one text item

=CONCATENATE(“GATE”,”WAY”) RETURNS GATEWAY

3. DOLLAR Converts a number to text, using the $ (dollar) currency format

=DOLLAR(10, 2) RETURNS $10.00

4. EXACT Checks to see if two text values are identical

A B

1 Word word

=Exact(A1,B1) Returns False

5. FIND Finds one text value within another (case-sensitive)

=Find(“quick”,”the quick brown fox”) Returns 5

6. LEFT Returns the leftmost characters from a text value

=Left(“Gateway”,4) Returns Gate

7. LEN Returns the number of characters in a text string

=Len(“Gateway”) Returns 7

8. LOWER Converts text to lowercase

=Lower(“EXCEL”) Returns excel

9. MID Returns a specific number of characters from a text string starting at the position you specify

=Mid(“information”,3,4) returns form

10. PROPER Capitalizes the first letter in each word of a text value

=Proper(“GATEWAY”) returns Gateway

11. REPLACE Replaces characters within text

=REPLACE("excel start",7,5,"end") returns excel end

12. REPT Repeats text a given number of times

=rept(“hello “,5) returns hello hello hello hello hello

13. RIGHT Returns the rightmost characters from a text value

=right(“Gateway”,3) Returns way

14. SEARCH Finds one text value within another (not case-sensitive)

=SEARCH("e",”Statements”,6)

Position of the first "e" in the first string above, starting at the sixth position (7)

15. SUBSTITUTE Substitutes new text for old text in a text string

=SUBSTITUTE(“Sales Data”, "Sales", "Cost") Returns Cost Data

16. UPPER Converts text to uppercase

=UPPER(“gateway”) returns GATEWAY

Text Manipulation Questions

(( Top ))

How can I get the first 5 characters from a cell?
A Use the "left" function. If the cell you want to extract the first 5 characters from is B5, the formula is =left(B5,5)

Q How can I get the last 5 characters from a cell?
A Use the "right" function. If the cell you want to extract the first 5 characters from is B5, the formula is =right(B5,5)

Q How can I get the middle 5 characters from a cell, starting at the third character?
A Use the "mid" function. If the cell you want to extract the first 5 characters from is B5, the formula is =mid(B5,3,5)

Q I have two cells, each containing part of an address. How can I combine these into one address?
A Use the "concatenate" function. If the cells you want to combine are A5 and B5, the formula is =A5&B5. (You can also use =concatenate(A5&B5) but why do the extra typing , right?)

Q How can I extract the first 5 characters after a dash "-" from a cell?
A Use the "find" function nested within the "mid" function. If the cell you want to extract 5 characters from is B5, the formula is =mid(B5,find("-",B5)+1,5). The "find" function will return the position number of the variable you are looking for, a dash in this example.

Q How can I convert a number that is formatted as text to a number that is formatted as numeric (sorting numbers formatted as text does not work)?
A Use the "value" function. If cell B2 contains the data you want to convert to a numeric format, the formula is =value(B2).

Q The data I have imported into my spreadsheet contains a bunch of trailing spaces, instead of "Davidson" the cell contains "Davidson_____" How do I get rid of the extra spaces?
A Use the "trim" function. If cell B2 contains the data you want to trim, the formula is =trim(B2). This function removes all spaces EXCEPT for single spaces between words.

Q How can I change the case in a cell from lower to all uppercase (all CAPITAL letters)?
A Use the "upper" function. If cell B2 contains the data you want to change the case, the formula is =upper(B2).

Q Just the opposite of the previous. How can I change the case in a cell from upper to all lowercase (all small letters)?
A Use the "lower" function. If cell B2 contains the data you want to change the case, the formula is =lower(B2).

Q One last question on case. How can I change the case in a cell from upper (or lower) to proper (first letter is capitalized and the rest are small letters)?
A Use the "proper" function. If cell B2 contains the data you want to change the case, the formula is =proper(B2). One caution on this, if you have a name like McDonald, then the "proper" function will return Mcdonald.

Example questions: of logical function

Q I want to find out if three conditions are true and, if they are, return the value "All Good". If any one of them is not true, I want to return the value "Errors". How do I do this without nesting "if" statements.
A Use the "and" function. If the cell you want to examine is B5 and the conditions you want are: it must be greater than cell B4, it must equal cell A5, and it must be less than cell A4 then the formula is
=if(and(B5>B4,B5=A5,B5

Q Same situation as above except that if any on the conditions are true I want to return the value "Good". If all of them are not true, I want to return the value "Errors".
A Use the "or" function. If the cell you want to examine is B5 and the conditions you want are: it must be greater than cell B4, OR it must equal cell A5, OR it must be less than cell A4 then the formula is
=if(or(B5>B4,B5=A5,B5

Try

The Functions Here