Function in MS Excel
Excel Function?
Excel functions are used to perform mathematical calculations. There are many built in function in excel which perform some specific task for example Sum . Average , Count etc. Functions are pre define formulas. The difference between function and simple regular formulas which is define by user is that Function perform mathematical and other calculation but not contain any operators, such as +, -, *, or /.
For example:
=Sum(A1:A10)
on the other hand
=A1+A2 is an regular formula.
Writing Function
Use an equal
sign to begin the function.
Specify the function name.
Enclose arguments within parentheses. Arguments are values on which you want
to perform the calculation. For example, arguments specify the numbers or
cells you want to add.
Use a comma to separate arguments.
=sum(A1:A10)
The Sum is name of the function and A1:A10 are argument which are reference range.
When we write function after the equal to sign then a list of the function name is appear. You can double-click on an item in the list to complete your entry quickly. Excel will complete the function name and enter the first parenthesis. You can also insert the function form Formula menu. Where different group are created for different operation. After inserting the function form formula bar the dialog box appear which ask for enter the argument after input all argument click on ok.
AND Function
The AND function is Logical Functions. The
output of AND function is either TRUE or FALSE. This function determine whether
the output will be TRUE or FALSE, the function evaluates at least one
mathematical expression located in another cell in the spreadsheet.
Example of AND Function
Note: This example is typed in cell C1. See the image above for help on this
example.
=AND (B1>100, B2>100, B3>100)
If all three of these cells (B1,B2, and B3) contain a value greater than 100,
the output for the AND function in cell C1 will be TRUE. If any of these cells
have numbers less than or equal to 100, the output for the AND function will be
FALSE.
FALSE Function
This function returns the logical value FALSE.
Syntax
FALSE( )
Average Function
The Average function calculate the average (arithmetic mean) of the given numbers.
Syntax of Average function :
Average( number1, number2, ... number_n or Range of Cell)
AverageA Function
The AverageA function returns the average (arithmetic mean) of the given numbers or Range. The AverageA function is differ from the Average function. The AVERAGEA will evaluate the logical values TRUE and FALSE and numbers represented as text on the other hand AVERAGE just skips these values during calculation.
For Example AVERAGE ("2",TRUE,6,8) returns 7 and AVERAGEA ("2",TRUE,6,8) returns 3.75.
Ceiling Function
The Ceiling function returns a number rounded up based on a multiple of significance. The syntax for the Ceiling function is:
=Ceiling( number, significance )
Where number is the number that you wish to round
up. significance is the multiple of significance that you wish to round a number
to. Note: If the sign of the number and the significance parameters are
different, the Ceiling function will return the #NUM! error.
Count Function
The Count Function in Excel counts the cells that contain numbers.
The syntax for the Count function is:
=Count( Cell Range)
For Example if you have value in Column A
10
12
34
5
6
Then =Count(A1:A5) will Return 5
Watch video tutorials of Count Function
CountA Function
The CountA function return The total number of Cell which contain both number and Text.
Syntax
=CountA(Cell Range )
For Example if you have value in Column A
10
12
B
6
Then =Count(A1:A4) will Return 4 where =Count function return 3.
CountBlank Function
The Excel CountBlank Function The number of Blank
cell given range of Cell.
The syntax for the CountBlank function is:
Syntax :
=CountBlank( range )
CountIf Function
The CountIf function counts the number of cells in a range that meets with given criteria.
Syntax:
CountIf( range, criteria )
Min Function
The Min function returns the smallest value from the given number or given range.
Syntax:
Min( number1, number2, ... number_n OR Cell Range )
MinA Function
MinA function returns the smallest value from the given values or Given cell Range. in MinA function values may be number, text or Logical values (True and False).
Syntax:
MinA( value1, value2, ... value_n OR Cell Range )
Power Function
The Power function returns the result of a number raised to a given power.
Syntax:
Power( number, power )
Product Function
The Product function multiplies the numbers and returns the product.
Syntax:
Product( number1, number2, ... number_n OR Cell Range)
IFERROR Function
In IFERROR you can specify any excel formula and error value. If function have some error then its return error value other wise Returns the result.
Syntax
IFERROR(value,value_if_error)
LOOKUP Function
In LOOKUP function you can find any value form one
column, one Row or Form Array giving criteria from other column.
Syntax
LOOKUP( value, lookup_range, [result_range] )
Watch Video Tutorial of Lookup function
VLOOKUP Function
The Excel VLOOKUP function is used when we want to search the value in one column (left most column) and return (display) the value form other column from the same row. in simple word we say that VLOOKUP function is used for a vertical lookup for the purpose of searching for a value in the left-most column of table and return a value from the same row in the index number position. where index number is the column number from which result display.
Syntax
VLOOKUP( value, table_array,
index_number, [not_exact_match] )
Read detail Tutorial about VLookup function
ABS Function
ABS function Returns the absolute value of a number.
Syntax:
ABS(Value, or Range)
Example:
ABS(-4) return 4
CONCATENATE Function
The CONCATENATE function combine the two string values. You can use values or two Cell range for Join. For example, if your worksheet contains a person's first name in cell A1 and the person's last name in cell B1, you can combine the two values in another cell by using the following formula:
=CONCATENATE(A1," ",B1)
The second argument in this example (" ") is a
space character. You must specify any spaces or punctuation that you want to
appear in the results as an argument that is enclosed in quotation marks.
Syntax
CONCATENATE(text1, [text2], ...)
NOT Function
Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.
Syntax
NOT(logical)
The NOT function syntax has the following arguments:
Logical Required. A value or expression that can be evaluated to TRUE or FALSE.
PMT Function
In this tutorials we are discus the one of the most important function of MS excel the financial function. This function called and use with name of PMT( ) function.This function is used for calculation like that monthly payment amounts on a loan .
Syntax
PMT(rate, nper, pv, fv, type)
Read Full tutorial of PMT function IN excel
SUBTOTAL Function
This function calculate the Subtotal (total of Sub list in The Large List) List.
Syntax
SUBTOTAL(function_num,ref1,[ref2],...])
SUM Function
The SUM function adds all the given values or
Given Range of Cells.
Syntax
SUM(number1,[number2],...] OR Cell Range)
Read more detail of SUM function
SUMIF Function
The Microsoft Excel SUMIF function adds all numbers in a range of cells, based on a single or multiple criteria. we can say that SUMIF Sum all the number which meet with our given criteria in the selected range. Where criteria is any logical expression.
Syntax:
SUMIF(range, criteria, [sum_range])
Read complete detail of SUMIF function
CLEAN Function
Removes all nonprintable characters from text. Use CLEAN on text imported from
other applications that contains characters that may not print with your
operating system. For example, you can use CLEAN to remove some low-level
computer code that is frequently at the beginning and end of data files and
cannot be printed.
Syntax
CLEAN(text)
LEFT, LEFTB Function
LEFT returns the first character or characters in a text string, based on the
number of characters you specify.
LEFTB returns the first character or characters in a text string, based on the
number of bytes you specify.
Syntax
LEFT(text, [num_chars])
LEFTB(text, [num_bytes])
IF Function
IF function is decision making function in this function we give a condition and on the behalf of that condition we make a decision. The If-Then function consists of three parts. First part is condition, Second part is what to do when the condition is true, Third part is what to do when the condition is false. The structure of the function is as follows:
=IF(condition,Result-If-true,Result
–If-false).
All the three part are separated by commas. If the result is in Alphabet format
then alphabet must be surrounded by quotes, numbers do not need to be surrounded
by quotes.
Now we take an Example:
If you have the student name and there marks of exam and you want calculate the
Result that which one student is fail and which one student is Pass. Then use
the IF function.
Spouse you have following table
| A | B | C | D | |
| 1 | Name | Marks | Result | |
| 2 | Qasim | 500 | ||
| 3 | Saleem | 150 | ||
| 4 | Ali | 560 | ||
| 5 | Faria | 200 | ||
| 6 | Majeed | 450 |
Now click on the C2 cell where you want to calculate the result.
Click on Formula tab in Office Ribbon and click on Logical function icon and then select If function from list.
In appear dialog box In logical text section give the your criteria (condition). For example spouse if any student have Grater than or equal to 300 marks then that student has result Pass other wise Fail. So Condition is B2>=300 where B2 is cell which contain marks.
Next text box is " Value-if True", in this box enter The Value if condition return True means any student have grater then 300 marks then what to do. for example " Pass ".
Next text box is "Value If False ', in this box enter the value if condition return false. means when any student have less then 300 marks the what to do. for example "Fail".
Click on Ok button.
You can also write this function manually as :
First of all click on cell where you want to write the function in our example C2.
Now write the If function as given below:
=IF (B2>=300,"Pass","Fail")
after writing above function press enter key.