Microsoft Excel - Functions

MS Excel Functions: Category wise

Date & Time:

a. DATE()

Returns the date's corresponding numeric representation in MS Excel date-time code.   

DATE(2001,11,25)                       - OUTPUT – 25-11-2001

b. NOW()

Returns the current time and date in a date and time format.

NOW() -                                      - OUTPUT – current date and time

c. TIME()

Converts the specified hours, minutes, and seconds into an Excel serial number with a time format.

TIME(8,27,30)                         - OUTPUT – 8:27 AM

d. TODAY()

Gives back the current date in date format.

TODAY()                                  - OUTPUT – today’s date (current date)

 

Math & Trig:

a. ABS() -

Give a number without a sign, which is the absolute value of the given integer.

ABS(-45)                                  - OUTPUT - 45

b. EVEN()

A positive number is rounded up, while a negative number is rounded down, to the closest even integer.

EVEN(41)                                 - OUTPUT - 42

c. FACT()

Brings back the factorial of a number, which is equivalent to 1*2*3*4*5.

FACT(5)                                   - OUTPUT - 120

d. GCD()

Gives the greatest or largest common divisor.

GCD(20,25)                             - OUTPUT - 5

e. INT()

A number is rounded to the nearest integer.

INT(44.8)                                 - OUTPUT - 44

f. LCM()

Provides the least common multiple.

LCM(10,7)                               - OUTPUT - 70

g. MOD()

Gives the remainder after a number is divided by a divisor.

MOD(10,7)                              - OUTPUT - 3

h. ODD()

A positive number is rounded up, and a negative number is rounded down, to the closest odd integer.

OOD(10)                                  - OUTPUT - 11

i. PI()

gives the Pi value, 3.14159265358979.

PI()                                           - OUTPUT - 3.14159265358979

j. POWER()

Returns the outcome of raising an integer to a power.

POWER(5,3)                            - OUTPUT – 125

 

k. PRODUCT()

Multiplies every number provided as an input.

PRODUCT(5,7,2)                     - OUTPUT – 70

l. QUOTIENT()

Gives the result of a division as an integer.

QUOTIENT(7,3)                       - OUTPUT – 2

m. RADIANS()

Degree to radians conversion.

RADIANS(45)                          - OUTPUT – 0.785398

n. ROMAN()

Converts an Arabic number into Roman letters.

ROMAN(15)                            - OUTPUT – XV

o. ROUND()

Returns a number rounded to a given number of digits.

ROUND(9.3,0)                         - OUTPUT – 9

ROUND(9.5,0)                         - OUTPUT – 10

p. ROUNDDOWN()

Rounding a number down, towards zero.

ROUNDDOWN(9.3,0)              - OUTPUT – 9

ROUNDDOWN(9.3,1)              - OUTPUT – 9.3

q. ROUNDUP()

Rounding a number up, away from zero.

ROUNDUP(9.3,0)                     - OUTPUT – 10

ROUNDUP(9.3,1)                     - OUTPUT – 9.3

 

r. SQRT()

Gives the number's square root back.

ROUND(16)                             - OUTPUT – 4

ROUND(10)                             - OUTPUT – 3.162277

s. SUM()

Adds all the numbers or adds all the numbers in a set of cells.

SUM(10,5,8)                            - OUTPUT – 23

SUM(A1:A5)                            - OUTPUT – add all the numbers in a range of cells.

t. SUMIF()

Adds the cells that are indicated by the given condition or criteria.

Example: D1=10, D2=10, D3=20, D4=20, D5=25

SUMIF(D1:D5,">10",D1:D5)                - OUTPUT – 65

 

Statistical:

a. AVERAGE()

Gives back the average (arithmetic mean) of its arguments.

AVERAGE(10,15,20)                            - OUTPUT - 15

b. AVERAGEIF()

Gives back the average (arithmetic mean) for the cells specified by a given condition or criteria.

Example: D1=10, D2=10, D3=20, D4=20, D5=25

AVERAGEIF(D1:D5,”>10”,D1:D5)                    - OUTPUT - 21.67

 

c. COUNT()

Determines how many cells in a given range contain numbers.

Example: D1=10, D2=10, D3=20, D4=20, D5=25

COUNT(D1:D5)                                                - OUTPUT – 5

COUNT(10,12,15)                                - OUTPUT – 3

d. COUNTA()

Calculates the number of non-empty cells in a range.

Example: D1=10, D2=10, D3=a, D4=20, D5=25

COUNTA(D1:D5)                                 - OUTPUT – 5

COUNTA(10,12,15,a)              - OUTPUT – 4

e. COUNTBLANK()

Calculates the number of empty cells inside a given range of cells.

Example: D1=10, D2=10, D3=, D4=20, D5=25

COUNTBLANK(D1:D5)                         - OUTPUT – 1

f. COUNTIF()

Counts the number of cells in a range that satisfy the specified condition.

Example: D1=10, D2=10, D3=20, D4=20, D5=25

COUNTIF(D1:D5)                                 - OUTPUT – 3

g. MAX()

The greatest value within a group of values is returned. Ignores text and logical values.

Example: D1=10, D2=10, D3=20, D4=20, D5=25

MAX(D1:D5)                                        - OUTPUT – 25

MAX(10,12,15)                                   -OUTPUT – 15

g. MIN()

Gives the value that is the least among a group of values. Disregards text and logical values.

Example: D1=10, D2=10, D3=20, D4=20, D5=25

 

MIN(D1:D5)                                         - OUTPUT – 10

MIN(8,12,15)                           - OUTPUT – 8

 

Text:

a. CONCATENATE()

Combine multiple text strings into a single text string.

CONCATENATE(“Sch”, “ool”)             - OUTPUT – School

b. DOLLAR()

Converts a number using the currency format to text.

DOLLAR(10)                - OUTPUT – Rs. 10.00

c. EXACT()

Returns TRUE or FALSE depending on if two text strings are exactly the same. Case matters when using EXACT.

EXACT(“school”, “school”)                  - OUTPUT – TRUE

d. FIND()

The starting location of one text string inside of another text string is returned. Case matters when using FIND.

FIND(“h”, “school”)                 - OUTPUT – 3

e. LEFT()

Returns the requested number of text characters starting at the beginning of the string.

FIND(“school”,4)                     - OUTPUT – scho (returns first four character in the string)

f. LEN()

Returns the length of a text string in characters. Gives the text string's total character count.

LEN(“computer”)                    - OUTPUT – 8

g. LOWER()

Makes all of the letters in a text string in lowercase.

LOWER(“COMPUTER”)                       - OUTPUT – computer

h. MID()

Returns the characters from the middle of a text string, given a starting position and length.

MID(“computer”,3,4)             - OUTPUT – mput

 

i. PROPER()

The first letter of each word in a text string is converted to uppercase, and all other characters are converted to lowercase, according to proper case.

PROPER(“the computer is good”)      - OUTPUT – The Computer Is  Good

PROPER(“tHE cOmPuter iS gOOD”)   - OUTPUT – The Computer Is  Good

j. REPLACE()

Replaces a portion of a text string with another text string.

REPLACE(“computer”,7,2, “in”)         - OUTPUT – computin

k. REPT()

Text is repeated a certain number of times.

REPT(“computer ”,4)  - OUTPUT – computer computer computer computer

l. RIGHT()

Returns the specified number of characters from the end of a text string.

RIGHT(“computer ”,3)            - OUTPUT – ter

m. SEARCH()

Returns the number of the character at which a specific character or text string is first found, reading left to right (not case-sensitive).

SEARCH(“P ”,computer,1)      - OUTPUT – 4

n. TRIM()

Makes a text string space-free, with the exception of single spaces between words.

TRIM(“This        is                       my              computer”)      

- OUTPUT – This is my computer

o. UPPER()

A text string is changed to all uppercase letters.

UPPER(“computer”)    - OUTPUT – COMPUTER

 

Logical: Logical function is used to perform logical test.

a. AND()

If all of its parameters are evaluated as TRUE, it returns TRUE; else, it returns FALSE.

AND(logical1, logical2)   - Syntax

AND(2+2=4, 2+3=5)    - OUTPUT – TRUE

AND(10>8, 10>15)      - OUTPUT – FALSE

b. IF()

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

IF(logical test, “value if true”, “value if false”)   - Syntax

IF(90>80, “A”, “B”)     - OUTPUT – A

c. NOT()

Changes FALSE to TRUE, or TRUE to FALSE,

NOT(10>20)                - OUTPUT – TRUE

 

 

d. OR()

Returns TRUE when any one of the conditions given is TRUE, and return FALSE only when all the given conditions are false.

OR(10>20, 10>8)         - OUTPUT – TRUE

 

CONVERT()

Converts a number from one measurement system to another.

=CONVERT(1,"in","cm")        - OUTPUT – 2.54 cm

 

If the input data types are incorrect, CONVERT returns the #VALUE! error value.

Unit names and prefixes are case-sensitive.

Macros

A macro is a sequence of computer instructions recorded and saved with a specified name. Macro help automate repetitive of complex task. The macro execute a set of commands that save time.

VIEW >> Macros >>   1. View Macros           2. Record Macros

Example :

Record a macro >> click on Record Macro (button)

Choose – Assign to macro (option) – 1. Button or 2. Keyboard

Then do a specific task, like – change the font face, change the font size, then stop Recording (button)

For execute or run a macro >> click on button (which you have created for assign a macro (button)

And the specific formatting is applicable to the selected text or write a new text with formatting.

 

Goal Seek

Finding the right input value when only the outcome is known is referred to as goal seeking.

It determines the value that you need to enter in a single input cell to produce a result that you want in a dependent (formula) cell.

Excel's Goal Seek function determines the input value (variable) depending on a specified result. The Goal Seek is a what-if analysis method that is used to investigate the effects of altering one value on another.

An important point to note is that the cell, whose reference is provided in the “set cell” box, should contain a formula.

Example – Goal Seek
Total                           =SUM(B2:D2)              - OUTPUT – 210
Percentage                 =(E2*100)/300                        - OUTPUT – 70
Using Goal Seek in this table:
Example 1:
- First select the cell E2 (total – must contain a formula)
- Then click on Data Tools >> What-If Analysis >> Goal Seek
- Goal Seek dialog box open and three option show.
1. Set cell – which already fill with cell reference - E2 (Total - contain a formula)
2. To value – write a value in this box, which you want to change the value of E2 cell
3. By changing cell – select a cell you want to change the value. (Test 1 or Test 2 or Test 3 – B2 or C2 or D2)
4. Then click on OK button to complete the function.
Then Goal Seek automatically changes the value in those cells.
Example 2:
- First select the cell F2 (total – must contain a formula)
- Then click on Data Tools >> What-If Analysis >> Goal Seek
- Goal Seek dialog box open and three option show.
1. Set cell – which already fill with cell reference – F2 (Percentage - contain a formula)
2. To value – write a value in this box, which you want to change the value of F2 cell
3. By changing cell – select a cell you want to change the value. (Test 1 or Test 2 or Test 3 – B2 or C2 or D2)
4. Then click on OK button to complete the function.
Then Goal Seek automatically changes the value in those cells.
 
Data Validation
To regulate the kind of data or values users submit into a cell, you can utilize data validation. For instance, you might choose to limit selections by employing a list, limit data entry to a specific set of dates, or ensure that only positive whole numbers are entered.
To stop users from inputting invalid data, you can set up data validation. If you'd like, you can let users enter incorrect information as long as you alert them when they try to type it in the cell. Messages that specify the type of input you anticipate from the cell as well as suggestions to assist users in fixing mistakes can also be supplied.
Data >> Data Tools >> Data Validation >> Data Validation
Click on Data Validation option - Three option show.
1. Settings >> Select the required cell or range of cells then fill the data validation criteria options.
2. Input Message >> Fill the input message option – the message show when cell is selected.
2. Error Alert >> Fill the Error message option – show the error alert after invalid data is entered.
Example : Data Validation
 
Select the cell range from cell B2:D5
Click on data validation tool option:
Settings >> Validation criteria

Allow – Whole number
Data – between (Logical comparison)
Minimun – 0
Maximum - 100
Input Message
Title – Marks Entry
Input Message – Test marks should be between 0 to 100.
Error Alert
Style – Stop / Warning / Information
Title – Marks Entry
Error Message – Test marks should be between 0 to 100.