Function and Operators

Function and Operators

Introduction - Generally Built-in-Formulas are called functions. Function is short form of formula. For example, if we want to sum the values ​​of cells A1 to A5, then the formula will be- A1+A2+A3+A4+A5. But by using Function, its short form can be made like this- Sum(A1:A5).

There are mainly the following functions in Excel-

1. Mathematical Function
2. Statistical Function
3. Date and Time Function
4. Text Function
5. Logical Function
6. Financial Function

Entering a Function in a Cell

Functions are used in two ways in Excel-

1. Type the function directly in the cell. But for this you should remember Function and their logic.
2. Select the desired function with the help of menu.

editing the function

Function can be edited if required. Follow these steps to edit the function-

1. Select the cell whose function is to be edited.
2. Press the 'F2' key or double click on that cell.
3. Make the desired modification. To select a new function, click on the Insert function on the Formulas menu.

Mathematical Functions

1. Sum() - This is a mathematical function. which returns the sum of the values ​​of the given range. 

Function - Sum(Range)

Example - =Sum(A1:A5) - This sums up the digits from cell A1 to A5.

2. ABS() - This is a mathematical function, which displays the integer or absolute value of the given number.

Function - ABS(Cell)

Example - =ABS(A7) - This shows the absolute value of the number present in cell A7. For example, cell A7 has "-5" then the function displays "5".

3. Average() - Calculates the numerical values ​​of the range of cells and displays their average.

Function - Average(Number1,Number2,Number3...........)

Example - =Average(A1:A6) - From this function the average of the marks of the selected range is calculated.

4. Ceiling() - Ceiling formula is used to round the number away from zero to its nearest multiple.

Function - Ceiling(Number, Significance)

Where 

Number is the value you want to round.

Significance is the multiple to which you want to round the number. If you use any number of Non-Numeric Argument, then the function "#Value!" (Error) displays. In this case two different symbols cannot be used.

Example - 

=Ceiling(169,7) = 175

=Ceiling(156,9) = 162

=Ceiling(177,8) = 184 will be displayed.

5. Even() - Returns the number rounded off to the nearest Even integer. This function can be used to process such numbers, which come in the form of two-two. E.g. even number.

Function - Even(Number)

Example - =Even(15) = 16

Similarly Odd Function is used for odd numbers.

Function- Odd(Number)

Example -=Odd(18) = 19

6. Fact() - This function is used to display the factorial of a number. The factorial of a number 1x2x3x4x5 is equal to the number ........ 

Function - Fact(Number)

Where Number is the non-negative number whose factor you want. If the number is Negative then this function "#Num!" (Error) displays.

Example - 

Fact(1)  = 1

Fact(2)  = 2

Fact(3)  = 6

Fact(-4)  = #Num!

Fact(5)  = 120

7. INT() - Rounds a number to the nearest integer less than itself.

Function - Int(Number)

Where Number is the real number that you want to equalize to an integer smaller than itself.

Example - 

=Int(4.5) = 4

=Int(8.9) = 8

8. Mod() - Displays the remainder after dividing a number by a divisor. The sign of the result is the same as that of the denominator. If the divisor is "0" the mod function returns an error "Div/0!

Function - Mod(Number, Divisor)

Where 

Number is the number for which the remainder is to be found.

Divisor is the number by which the given number is to be divided.

9. Power() - Displays the power result of a number.

Function- Power(Number, Power)

Where

Number is Aadhar number, it can be any real number.

Power is the exponent to which the base number is raised.

Example - 

=Power(25,2) = 625

=Power(13,2) = 169

=Power(15,2) = 225

10. Sumif() - Joins the specified cells according to the given parameters.

Function - Sumif(Range,Criteria,Sum_Range)

Where

Range is the range of cells you want to add.

Criteria is a parameter that can be in the form of numbers or text and defines the number of cells to be joined.

Sum_Range is the actual cell whose sum is to be calculated.

Statistical Function

1. Max() - This function is used to display the maximum value out of the values ​​present in the Range.

Function- Max(Range)

Example :- =Max(12,19,71,68,36) = 71, because this is the maximum value of range.

2. Min() - Displays the minimum value out of the values ​​present in the Range.

Function - Min(Range)

Example :- =Min(12,8,9,61,4) = 4 because this is the minimum value of range.

3. Count() - This function tells how many cells in the range have numerical data. 

Function :- Count(Value1,Value2,Value3,Value4............)

4. Sqrt() - It displays the square root of a positive number, if the number is negative, it will say "#Num!" (Error) displays.

Function :- SQRT(Number)

Where 

Number means the value entered in the cell whose square root you want to extract.

Example:- 

=SQRT(25) = 5

=SQRT(81) = 9

5. Countif() - It counts those values ​​within the range which are according to the given criteria.

Function - Countif(Range,Criteria)

Where 

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

Criteria - Criteria in the form of a number or text that defines which cells will be counted.

Using Date and Time Functions

1. Day() - Displays the day of the date, which is in the form of a serial number, any integer between days 1 to 31.

Function :- Day(Date)

Where 

date is the date from which you want to extract the day. The date which can be entered inside the quotation mark as a text string.

Example;- 

1. =Day("01-apr") = 01 displays.
2. =Day("22-Jul-2019")  = 22 displays.

2. Date() - This function displays a number, which represents a particular number.

Function :- Date(Year,Month,Day)

Where 

Year - It can be in 1 to 4 digits.

Month - This number represents the month of the year, if the month to be input is greater than 12, then any number greater than 12 is added to the next year accordingly. eg - date(1996,14,2) represents the number which represents Feb2,1997.

Day - This number represents the day of the month. If the day is greater than the days of that particular month, then those days are added to the days of the next month. 

Ex :- =date(1996,1,35) = shows Feb4,1996.

3. Now() - Displays the current system date.

Function - =Now()

Ex:- Now() displays the current time when this function is run.

4. Today() - Displays the current system date.

Function :- Today()

Ex:- Today() displays the current date on running this formula.

5. Left() - This function returns the letters based on the number given from the left.

Function:- Left(Text,Num_Chars)

Where

Text is the word whose letters are to be extracted.

Num_Chars is the number you want to take from that word on the left.

Ex:- =Left(F1,3)- If "Tajmahal" is written in F1 then it displays the function "Taj".

6. Right() - This function displays the letters based on the number given from the right side. 

Function :- =Right(Text,Num_Chars)

7. Len() - Displays the total number of letters of the word present in the cell. 

Function :- Len(Text)

Ex:- =len("ITCEC") =5, because there are 5 characters in the quotation mark.

8. Dollar() - Converts the number to text using the currency format, and rounds the decimal to the specified position.

Function :- Dollar(Number,Decimal)

Where 

Number is a number, or a reference to a cell that contains a number or a formula, which gives a number in the form of its mind.

Decimal - Decimal is the number of digits to the right of the point. If the decimal is negative, the number will be rounded to the left of the decimal point. But to remove the decimal, you should write "0" in place of the decimal.

Ex:- 

=Dollar(589,-2) = displays $600.

=Dollar(589,2) = displays $589.00. 

9. Char() - Displays the character specified by a number. Use CHAR to translate those code page numbers into characters. Which you can get from files present on other types of computers.

Function:- =Char(Number)

Where

Number is the number between 1 to 255 that represents the character you want, this character will be any character from the character set used by your computer.

Ex :- 

=Char(80) - This displays 'P', the same result as alt+80. 

=Char(66) - Displays 'B', the same result as alt+66.

10. Concatenate() - It combines multiple text strings into one text string. It is similar to function and operator, which can also be used to join text strings.

function :- =Concatenate(Text1,Text2,Text3...........)

Where 

Text1, Text2, Text3....... are text items from 1 to 30 that can be combined into a single text temp. Text can be added to the item. A text item can contain a text string, numbers, or a single cell reference. 

11. Trim() - Removes all spaces in the text except one space between words. Use 'Trim' on that text. Which you have obtained from other application, and which may contain irregular spaces.

function :- =Trim(Text)

Where 

Text is the text from which you want to remove the spaces.

Logical Functions

1. If() - Displays two types of values, one when the condition given by you is true or the other when the condition given by you is false.

If() is used to check values ​​and formulas based on condition.

Function :- If(Logical_Test,[Value_if_True],[Value_if_False])

Where 

Logical_Test - Any value or expression that can be true or false.

Value_if_True - is the value that is obtained if Logical_Test is true.

Value_if_False - is the value that is obtained when Logical_test is false.

Ex:- 

Cell 'F9' in a marksheet contains the marks of the student, if the value of 'F9' is more than 190 then the result of the formula should be "Pass" otherwise "Fail". In this case the formula will be written as-

=if(F9>190,"Pass","Fail")

Financial Function

1. PMT () - This function calculates for the loan, on the basis of constant payments and interest rate interest rate.

Syntax :- PMT(Rate,nPer,Pv,Fv,Type)

Where 

Rate - is the interest rate charged on the loan. 

nPer - is the total number of payments for the repayment of the loan, that is, in how many installments the loan will be repaid, it is its total number.

Pv - Pv refers to the principal or the total amount, which is the present value of a series of payments to be made in the future. 

Fv - Fv refers to the future value or cash balance (cash balance). What you want to get, when the last amount is paid. If Fv is omitted, it will mean that the Future value is "0". 

Type - ranges from 0 or 1, indicating when the payment is due. Setting 0 or leaving it blank means that the payment is to be made at the end of the period. If payment is to be made at the beginning of the period then set "1".

Ex:- 

1 lakh Rs. The amount is to be paid in 10 months at the rate of 8% per annum.

Syntax :- PMT(8%,100000,0,1)

If the payment is to be made at the beginning of the period then the amount payable will be Rs.10301.64. Will be 

Syntax :- PMT(8%,100000,0,0)

10370.32 if payment is to be made at the end of the period. will have to pay.