Microsoft Office

# Formula & Function in MS-Excel

Formula & Function in MS-Excel-:A Formula is an expression which calculate the value of a cell. Functions are predefined formula and are already available in Excel.

Using a formula, expressions can be calculated by typing the formula in the appropriate cell:-

For example:- D3+ E6 Asia formula that adds the cell content of cell D3 to the cell content of self E6. The formula must be preceded by an equal to (=) sign. You can calculate different expressions using different operators in MS-Excel.

#### Functions

A Function is a three define formula that perform calculation using specific value in a particular order. Example SQRT (), LOWER (), etc. Functions make it easy to perform common calculations on data.

##### Using Function

To use the function in work sheet, you have to fo-llow the follow steps:

• Active the cell in which you want to enter the formula.
• Two type the formula with the help of function click on auto some button on the standard toolbar.
• Click on the down arrow head write next to the function box to invoke a drop down list of available function which included SUM, COUNT, AVERAGE and MAX.
• Click on the function you want to add the formula. If the function does not appeared in the list click on more function.
• Enter the arguments for the function.
• When you completely formula press the enter key.
• The Other convenient way of choosing function is by using the function that appear on the standard toolbar as fx symbol.

#### Function

• Today:- It is used to display the current date.
• Weekday:-  this function is used to display the day of week corresponding given date.
• Days360:- this company is used to display the number of day between 2 Days.
• Rept:- Function is used to Rept the given text to a specified number of times.
• Round:- It is used for round the value if 5 or above 5 by adding 1 in the left digit and if below 5 without adding 1 round the decimal value up to given place if given place negative round the integer value.
• Count:- It is used to count the numeric and alphabet both.
• Countify:- Is used to count only given value (criteria).
• Product:- This multiple of given numbers.
• Power:-  the number raises to a given power.
• Mod:- This function return the remainder of a division.

#### Functions in MS Excel

• SUM():- It is a mathematical function used to add the numeric value in a range of cells.  A maximum of 30 arguments can be provided. Syntax = SUM (number 1, number 2,…….)
• AVERAGE():- Calculates and return the average of the numeric values in the range of cells. Syntax = AVERAGE (number 1, number 2,…….)
• MAX ():- Returns the largest value in the range. Syntax = MAX (number 1, number 2,……….)
• MIN ():- Returns the lowest value in the range. Syntax = MIN (number 1, number 2,…….)
• COUNT():- Counts the number of cells that contain number in a specified range. Syntax = COUNT (value 1, value 2,…….)
• ABS():- ABS () function return the absolute value of number the absolute value of number is the number without any + or – sign. Syntax = ABS (number 1, number 2,…….)
• SQRT ():- SQRT () returns a positive square root a number. Syntax = SQRT (number 1, number 2,…….)
• NOW() :- Returns the current date and time value return by Now() function will change each time when worksheet is recalculated. Syntax = NOW (number 1, number 2,…….)
• LEFT () :- Returns the specified number of characters on the left side of a tax exchange if the number is not a specified that return the first character. Syntax = LEFT (number 1, number 2,…….)
• RIGHT () :- Returns the specified number of character from the right side of a text string if the number is not specified it return the last character. Syntax = RIGHT (number 1, number 2,…….)
• MID() :- Returns as specified number of character is starting from a specified position in a text string. Syntax = MID (number 1, number 2,…….)
• UPPER():- Converts all lowercase letters in a text string to uppercase. Syntax = UPPER (number 1, number 2,…….)
• LOWER () :- Converts all the uppercase letter in a text string to lowercase. Syntax = LOWER (number 1, number 2,…….)
• LEN () :- Returns the length of a text string. Spaces are counted as character. Syntax = LEN (number 1, number 2,…….)
• PROPER():- Convert the first letter of each word in a string to upper case and remaining letters of lowercase. Syntax = PROPER (number 1, number 2,…….)

#### Formula Operators

There are four different types of formula operators: arithmetic, comparison, text concatenation and reference.

Arithmetic Operators:- To perform basic mathematical operation such as addition, subtractions, multiplication, combine number and produce numeric results, arithmetic operators are used. Some arithmetic operators and their meaning are as follows:

A Formula is an expression which calculate the value of a cell. Functions are predefined formula and are already available in Excel.

Using a formula, expressions can be calculated by typing the formula in the appropriate cell:-

For example:- D3+ E6 Asia formula that adds the cell content of cell D3 to the cell content of self E6. The formula must be preceded by an equal to (=) sign. You can calculate different expressions using different operators in MS-Excel.

#### Functions

A Function is a three define formula that perform calculation using specific value in a particular order. Example SQRT (), LOWER (), etc. Functions make it easy to perform common calculations on data.

##### Using Function

To use the function in work sheet, you have to follow the follow steps:

• Active the cell in which you want to enter the formula.
• Two type the formula with the help of function click on auto some button on the standard toolbar.
• Click on the down arrow head write next to the function box to invoke a drop down list of available function which included SUM, COUNT, AVERAGE and MAX.
• Click on the function you want to add the formula. If the function does not appeared in the list click on more function.
• Enter the arguments for the function.
• When you completely formula press the enter key.
• The Other convenient way of choosing function is by using the function that appear on the standard toolbar as fx symbol.

#### Function

• Today:- It is used to display the current date.
• Weekday:-  this function is used to display the day of week corresponding given date.
• Days360:- this company is used to display the number of day between 2 Days.
• Rept:- Function is used to Rept the given text to a specified number of times.
• Round:- It is used for round the value if 5 or above 5 by adding 1 in the left digit and if below 5 without adding 1 round the decimal value up to given place if given place negative round the integer value.
• Count:- It is used to count the numeric and alphabet both.
• Countify:- Is used to count only given value (criteria).
• Product:- This multiple of given numbers.
• Power:-  the number raises to a given power.
• Mod:- This function return the remainder of a division.

#### Functions in MS Excel

• SUM():- It is a mathematical function used to add the numeric value in a range of cells.  A maximum of 30 arguments can be provided. Syntax = SUM (number 1, number 2,……….)
• AVERAGE():- Calculates and return the average of the numeric values in the range of cells. Syntax = AVERAGE (number 1, number 2,……….)
• MAX ():- Returns the largest value in the range. Syntax = MAX (number 1, number 2,……….)
• MIN ():- Returns the lowest value in the range. Syntax = MIN (number 1, number 2,……….)
• COUNT():- Counts the number of cells that contain number in a specified range. Syntax = COUNT (value 1, value 2,……….)
• ABS():- ABS () function return the absolute value of number the absolute value of number is the number without any + or – sign. Syntax = ABS (number 1, number 2,…….)
• SQRT ():- SQRT () returns a positive square root a number. Syntax = SQRT (number 1, number 2,…….)
• NOW() :- Returns the current date and time value return by Now() function will change each time when worksheet is recalculated. Syntax = NOW (number 1, number 2,…….)
• LEFT () :- Returns the specified number of characters on the left side of a tax exchange if the number is not a specified that return the first character. Syntax = LEFT (number 1, number 2,…….)
• RIGHT () :- Returns the specified number of character from the right side of a text string if the number is not specified it return the last character. Syntax = RIGHT (number 1, number 2,…….)
• MID() :- Returns as specified number of character is starting from a specified position in a text string. Syntax = MID (number 1, number 2,…….)
• UPPER():- Converts all lowercase letters in a text string to uppercase. Syntax = UPPER (number 1, number 2,…….)
• LOWER () :- Converts all the uppercase letter in a text string to lowercase. Syntax = LOWER (number 1, number 2,…….)
• LEN () :- Returns the length of a text string. Spaces are counted as character. Syntax = LEN (number 1, number 2,…….)
• PROPER():- Convert the first letter of each word in a string to upper case and remaining letters of lowercase. Syntax = PROPER (number 1, number 2,……….)

#### Formula Operators

There are four different types of formula operators: arithmetic, comparison, text concatenation and reference.

Arithmetic Operators:- To perform basic mathematical operation such as addition, subtractions, multiplication, combine number and produce numeric results, arithmetic operators are used. Some arithmetic operators and their meaning are as follows:

A Formula is an expression which calculate the value of a cell. Functions are predefined formula and are already available in Excel.

Using a formula, expressions can be calculated by typing the formula in the appropriate cell:-

For example:- D3+ E6 Asia formula that adds the cell content of cell D3 to the cell content of self E6. The formula must be preceded by an equal to (=) sign. You can calculate different expressions using different operators in Ms-Excel.

#### Functions

A Function is a three define formula that perform calculation using specific value in a particular order. Example SQRT (), LOWER (), etc. Functions make it easy to perform common calculations on data.

##### Using Function

To use the function in work sheet, you have to follow the follow steps:

• Active the cell in which you want to enter the formula.
• Two type the formula with the help of function click on auto some button on the standard toolbar.
• Click on the down arrow head write next to the function box to invoke a drop down list of available function which included SUM, COUNT, AVERAGE and MAX.
• Click on the function you want to add the formula. If the function does not appeared in the list click on more function.
• Enter the arguments for the function.
• When you completely formula press the enter key.
• The Other convenient way of choosing function is by using the function that appear on the standard toolbar as fx symbol.

#### Function

• Today:- It is used to display the current date.
• Weekday:-  this function is used to display the day of week corresponding given date.
• Days360:- this company is used to display the number of day between 2 Days.
• Rept:- Function is used to Rept the given text to a specified number of times.
• Round:- It is used for round the value if 5 or above 5 by adding 1 in the left digit and if below 5 without adding 1 round the decimal value up to given place if given place negative round the integer value.
• Count:- It is used to count the numeric and alphabet both.
• Count if:- Is used to count only given value (criteria).
• Product:- This multiple of given numbers.
• Power:-  the number raises to a given power.
• Mod:- This function return the remainder of a division.

#### Functions in MS Excel

• SUM():- It is a mathematical function used to add the numeric value in a range of cells.  A maximum of 30 arguments can be provided. Syntax = SUM (number 1, number 2,……….)
• AVERAGE():- Calculates and return the average of the numeric values in the range of cells. Syntax = AVERAGE (number 1, number 2,……….)
• MAX ():- Returns the largest value in the range. Syntax = MAX (number 1, number 2,……….)
• MIN ():- Returns the lowest value in the range. Syntax = MIN (number 1, number 2,……….)
• COUNT():- Counts the number of cells that contain number in a specified range. Syntax = COUNT (value 1, value 2,……….)
• ABS():- ABS () function return the absolute value of number the absolute value of number is the number without any + or – sign. Syntax = ABS (number 1, number 2,…….)
• SQRT ():- SQRT () returns a positive square root a number. Syntax = SQRT (number 1, number 2,…….)
• NOW() :- Returns the current date and time value return by Now() function will change each time when worksheet is recalculated. Syntax = NOW (number 1, number 2,…….)
• LEFT () :- Returns the specified number of characters on the left side of a tax exchange if the number is not a specified that return the first character. Syntax = LEFT (number 1, number 2,…….)
• RIGHT () :- Returns the specified number of character from the right side of a text string if the number is not specified it return the last character. Syntax = RIGHT (number 1, number 2,…….)
• MID() :- Returns as specified number of character is starting from a specified position in a text string. Syntax = MID (number 1, number 2,…….)
• UPPER():- Converts all lowercase letters in a text string to uppercase. Syntax = UPPER (number 1, number 2,…….)
• LOWER () :- Converts all the uppercase letter in a text string to lowercase. Syntax = LOWER (number 1, number 2,…….)
• LEN () :- Returns the length of a text string. Spaces are counted as character. Syntax = LEN (number 1, number 2,…….)
• PROPER():- Convert the first letter of each word in a string to upper case and remaining letters of lowercase. Syntax = PROPER (number 1, number 2,……….)

#### Formula Operators

There are four different types of formula operators: arithmetic, comparison, text concatenation and reference.

Arithmetic Operators:- To perform basic mathematical operation such as addition, subtractions, multiplication, combine number and produce numeric results, arithmetic operators are used. Some arithmetic operators and their meaning are as follows:

Comparison Operators:- You can compare two values with the following operators. When two  values are compared by using these operator, the result is a logical value either TRUE or FALSE. Some comparison operators and their meaning as follows:-

Text Concatenation Operators:- Use the ampersand (&) to concatenate (combine) one or more text strings to produce a single piece of text.

Reference Operators:- To combine the range of cells for various calculations, reference operators are used. Some reference operators and their meaning are as follows:- 