Microsoft Office

Formula & Function in MS-Excel

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: 

Arithmetic OperatorsMeaning
+(plus sign) Addition
-(minus sign)Subtraction or negative
*(asterisk)Multiplication
/(forward slash)Division
^(caret)Exponentiation

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:- 

Comparison OperatorsMeaning
=(equal sign)Equal to
>(greater than sign)Greater Than
<(less than sign)Less Than
>= (greater than or equal to sign )Greater than or equal to
<= (less than or equal to sign )Less than or equal to
<>, ! = (not equal to sign )Not equal to

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

Text Operators Meaning
&(ampersand)Connects or concatenates two strings to produce one continuous text value

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

Reference Operators Meaning
:(colon)Range operator which produces one reference to all the cells between two references
,(comma)Union operator which combine multiple reference into one use reference
(space)Intersection operators which returns a reference to the cells common to the ranges in the formula

You can also join us at QUORA

About the author

Pooja Rastogi

Leave a Comment