Contents
Basic symbol about Function and Formulas ................................................................................................. 1
Mathematical Function ................................................................................................................................. 1
LOGICAL FUNCTION ...................................................................................................................................... 8
Statistical Function ........................................................................................................................................ 9
Text Function .............................................................................................................................................. 11
Date and Time Function .............................................................................................................................. 14
Financial Function ....................................................................................................................................... 15
Basic symbol about Function and Formulas
( :- Open parenthesis
) :- Close Parenthesis
, :- Comma (Particular cell dks define djrk gSA)
: :- Colon (Cell Range dks define djrk gSA)
() :- Function symbol
(……) :- Function ds vUnj fy[ks x;s data dks argument dgrs gSaA
“ :- Double inverted comma
Mathematical Function
1. Sum():-The Excel SUM function returns the sum of values supplied as multiple arguments.
(a;g function argument esa ikl fd;s x;s multiple number dks tksM+rk gSA)
Syntax =SUM (number1, [number2], [number3], ...)
Example =sum(20,60,50) Equals 130
Page1
=sum(b2,c2,d2,e2,f2,g2) OR, =sum(b2:g2)
uksV%& lHkh vadksa dks ,d lkFk tksM+us ds fy, (Alt+=) nck;sa
2. ABS():- The Excel ABS function returns the absolute value of a number. Negative numbers
are converted to positive numbers, and positive numbers are unaffected.
Syntax:- =ABS (number)
Example:- =ABS(-3) its returns 3
3. Decimal():- The Excel DECIMAL function converts an alpha-numeric number into its
decimal equivalent.
Syntax:- =DECIMAL(number, radix)
Example:- = decimal(11,2) equals 3
=decimal(77,8) equals 63
Page2
4. Even():- The Excel EVEN function returns the next even integer after rounding a given
number up. The EVEN function always rounds numbers up (away from zero) so positive
numbers become larger and negative numbers become smaller
(i.e. more negative).
Syntax:- = EVEN (number)
Example:- =even(3) equals 4, =even(0.3) equals 2
5. Fact():-The Excel FACT function returns the factorial of a given number.
Syntax:- =fact(number)
example, =FACT(3) returns 6, equivalent to 3 x 2 x 1.
Page3
6. INT():-INT function returns the integer part of a decimal number by rounding down to the
integer. Note the INT function rounds down, so negative numbers become more negative.
For example, while INT(10.8) returns 10, INT(-10.8) returns -11.
Syntax:- =INT(number)
Example:- =Int(4.3) equals 4, =int(6.7) equals 6.
7. LCM():-LCM function returns the least common multiple of integers. The least common
multiple is the smallest positive integer that is a multiple of all supplied numbers. For
example, =LCM(25,40) returns 200.
Syntax:- =LCM(Number1,number2,number3,….)
Example:- =LCM(9,6) equals 18, =LCM(30,20) equals 60.
8. MOD():-MOD function returns the remainder of two numbers after division. For example,
=MOD(10,3) equals 1. The result of MOD carries the same sign as the divisor.
Syntax:- =Mod(number,divisor).
Example:- =mod(64,6) equals 4
Page4
9. ODD():-ODD function returns the next odd integer after rounding a given number up. The
ODD function always rounds numbers up (away from zero) so positive numbers
become larger and negative numbers become smaller (i.e. more negative).
Syntax:- =ODD(number)
Example:- =ODD(3.4) equals 5, =ODD(8) equals 9.
10. POWER():-POWER function returns a number to a given power. The POWER function
works like an exponent in a standard math equation.
Syntax:- =POWER(number,power)
Example:- =POWER(5,2) equals 25, and =Power(7,3) equals 343.
Page5
11. PRODUCT():-PRODUCT function returns the product of numbers provided as arguments.
The PRODUCT function is helpful when When multiplying many cells together
Syntax:- =PRODUCT(number1,number2…………….).
Example:- =PRODUCT(3,4,5) equals 60. =PRODUCT(A2,B2,C2). =PRODUCT(A2:C2).
12. ROMAN():-ROMAN function converts a number to a Roman numeral as text. For example,
the formula =ROMAN(4) returns IV.
Syntax:- =Roman(number)
Example:- =Roman(4) equals IV. Roman(10) equals X.
13. ROUND():- ROUND function returns a number rounded to a given number of digits. The
ROUND function can round to the right or left of the decimal point.
Syntax():- =Round(number,num_digit)
Example:- =Round(35.56,1) equals 35.6, =Round(10.54,1) equals 10.5. Page6
14. ROUNDDOWN():-ROUNDDOWN function returns a number rounded down to a given
number of decimal places.
Syntax:- =Rounddown(number,num_digit)
Example:- =Rounddown(35.56,1) equals 35.5, =Rounddown(44.61,1) equals 44.6.
15. ROUNDUP():-ROUNDUP function returns a number rounded up to a given number of
decimal places.
Syntax:- =Roundup(number,num_digit)
Example:- =Roundup(35.56,1) equals 35.6, =Roundup(44.61,1) equals 44.7.
16. SQRT():- SQRT function returns the square root of a positive number. SQRT returns an
error if number is negative.
Syntax():- =Sqrt(number)
Example:- =Sqrt(25) equals 5, =Sqrt(144) equals 12.
17. SUMIF():-SUMIF is a function to sum cells that meet a single criteria. SUMIF can be used to
sum cells based on dates, numbers, and text that match specific criteria. SUMIF supports
logical operators (>,<,<=,>=) and wildcards (*,?) for partial matching.
Syntax:- =sumif(Range,”Criteria”,Sum_range)
Example:- =Sumif(b2:b8,”Arwal”,c2:c8) equals 11,700.
Page7
LOGICAL FUNCTION
18. AND():-AND() returns either TRUE or FALSE, According to Given Arguments.
Syntax:- =AND(Logical1,Logical2..)
Example:- =AND(A2>5,A2<90)
19. OR():-OR()returns either TRUE or FALSE, According to Given Arguments.
Syntax:- =OR (Logical1,Logical2..)
Example:- =OR(a2=”Red”,a2=”Green”)
20. IF():-The IF function can perform a logical test and return one value for a TRUE result, and
another for a FALSE result.
Page8
Syntax:- = IF (logical_test,value_if_true,value_if_false)
Example:- =if(L3>=150,”Pass”,”Fail”)
Example:- =if(L3>=300,”1st”,if(L3>=225,”2nd”,if(L3>=150,”3rd”,”Fail”)))
Example:- = if(m3<30,”Fail”,if(L3>=300,”1st”,if(L3>=225,”2n”,”3rd”)))
Statistical Function
21. MIN():-The MIN function returns the smallest numeric value in a range of values.
Syntax:- =min(number1,number2………)
Example:- =min(75,64,68,86,87) equals 64.
Example:- =min(c2:h2) equals 64
22. MAX():-The MAX function returns the largest numeric value in a range of values.
Syntax:- =max(number1,number2………)
Example:- =max(75,64,68,86,87) equals 87.
Example:- =max(c2:h2) equals 87
Page9
23. AVERAGE():-The Excel AVERAGE function returns the average of values supplied as
multiple arguments.
Syntax:-=average(number1,number2,number3……………….)
Example:- =average(75,64,68,64,86) equals 71.4
Example:- =average(c2:g2) equals 71.4.
24. COUNT():- The Count function counts the number of cells that contain the numbers.
Syntax:- =Count(Value1,value2,………….)
Example:- =Count(a2:a8) equals 3.
25. COUNTA():- The COUNTA function counts the total number of cell in cell range. COUNTA
function does not count empty cells.
Syntax:- =COUNTA(Value1,Value2,…………..)
Example:- =COUNTA(A2:A8) equals 6.
26. COUNTBLANK():- The countblank function count the empty cells in the specified cell
range.
Syntax:- =COUNTBLANK(Range).
Example:- =COUNTBLANK(A2:A8) equals 1.
Page10
27. Countif():-COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be
used to count cells with dates, numbers, and text that match specific criteria. The
COUNTIF function supports logical operators (>,<,<=,>=) and wildcards (*,?) for partial
matching.
Syntax:- = Countif(Range, Criteria)
Example:- =countif(c3:c9,”>310”)
Example=:- =countif(A3:A9,”=Mouse”)
Text Function
28. Concatenate():-The Excel CONCATENATE function concatenates (joins) join up to 30 text
items together and returns the result as text.
Syntax:- =concatenate(text1,text2,text3…..)
Example:- =concatenate(“Ram”,”Kumar”)
Page11
29. Left():-The Excel LEFT function extracts a given number of characters from the left side of a
supplied text string.
Syntax:- =left(Text,Num_char)
Example:- =left(“INDIA”,3) Equals IND.
Example:- =left(a16,3)
30. Right():-The Excel RIGHT function extracts a given number of characters from the right side
of a supplied text string.
Syntax:- =RIGHT (text, num_chars)
Example:- =right(“INDIA”,3) Equals DIA
Example:- =right(a16,3)
31. MID():-The Excel MID function extracts a given number of characters from the middle of a
supplied text string.
Syntax:- =MID (text, start_num, num_chars)
Example:- =MID(“INDIA”,2,3) Equals NDI
32. LEN():-The Excel LEN function returns the length of a given text string as the number of
characters. LEN will also count characters in numbers, but number formatting is not
included.
Syntax:- =LEN(Text)
Page12
Example:- = LEN(“Computer”) Equals 8
33. Lower():-The Excel LOWER function returns a lower-case version of a given text string.
Numbers and punctuation are not affected.
Syntax:- =Lower(Text)
Example:- =Lower(“COMPUTER”) Equals computer.
34. Proper():-The Excel PROPER function capitalizes words given text string. Numbers and
punctuation are not affected.
Syntax:- =Proper(Text)
Example:- =(“rahul kumar”) equals Rahul Kumar
35. UPPER():-The Excel UPPER function returns a upper-case version of a given text string.
Numbers and punctuation are not affected.
Synatax:- =UPPER(Text)
Example:- =UPPER(“computer”) equals COMPUTER.
36. REPT():-The Excel REPT function repeats characters a given number of times.
Syntax:- =Rept(Text,Num_times)
Example:- = Rept(“apple”,2) equals appleapple.
Page13
Date and Time Function
37. Today():- Today function returns the current date of the system.
Syntax:- =today() (Ctrl+;)
38. Now():- It returns the current time of the system
Syntax:- =now() (Ctrl+:), (Ctrl+Shift+;)
39. Day():- It returns the day of a date passed as a arguments. The day is given as integer
ranging from 1to31.
Syntax:- =day(“Serial_number”)
Example:- =day(“2019/05/10”) equals 10
Note:- Where Serial_number is the date of the day you are trying to find.
40. Month():- It returns the month of date which is passed as arguments. The month is given
as an integer, ranging from 1to12(1 for January, 12 for December)
Syntax:- =month(“Serial_number”)
Example:- =month(“2019/05/10”) equals 05
41. Year():- It returns the year corresponding to a date.
Syntax:- =Year(“Serial_Number”)
Example:- = year(“2019/05/10”) equals 2019
42. Weekday():- This function returns an integer from 1 to 7 (Where 1 for Sunday, 7 for
Saturday).
Syntax:- =weekday(“Date”)
Example:- =weekday(“20/04/2019”) equals 7
Page14
43. Days360():- This function calculates the number of days between two dates.
Syntax:- =days360(“Date1”,”Date2”)
Example:- =days360(“20/04/2019”,”18/05/2019”) equals 28.
Financial Function
44. SLN():-The Excel SLN function returns the depreciation of an asset for one period,
calculated with a straight-line method. The calculated depreciation is based on initial asset
cost, salvage value, and the number of periods over which the asset is depreciated.
Syntax:- =SLN(Cost, Salvage, Life)
Where:- cost - Initial cost of asset.
salvage - Asset value at the end of the depreciation.
life - Periods over which asset is depreciated.
Example:- =SLN(10000,6000,4) equals 1000
=SLN(A54,B54,C54) equals 1000.
Page15
45. DB():-The Excel DB function returns the depreciation of an asset for a specified period
using the fixed-declining balance method.
Syntax :- =DB (cost, salvage, life, period, [month])
Where:-
cost - Initial cost of asset.
salvage - Asset value at the end of the depreciation.
life - Periods over which asset is depreciated.
period - Period to calculation depreciation for.
month - [optional] Number of months in the first year. Defaults to 12.
Example :- =DB(20000,2000,6,3) Equals $2,958.00.
Example:- =DB(B60,C60,D60,E60)
Example:- =DB($B$60,$C$60,$D$60,E60)
46. PMT():-The Excel PMT function is a financial function that returns the periodic payment for
a loan.
Syntax:- =PMT(Rate, N_Per,Loan_Amount)
Example:- =PMT(7%/12,3,54000) Equals 18,210.41
Page16
47. FV():-The Excel FV function is a financial function that returns the future value of an
investment. You can use the FV function to get the future value of an investment assuming
periodic, constant payments with a constant interest rate.
Syntax:- =FV (rate, nper, pmt)
Example:- =fv(b74/12,c74,a74).
48. VLOOKUP():- VLOOKUP Microsoft excel esa ,d important function. & bl function ds
}kjk cM+s MkVk “khV esa ,d fo”ks’k Value dks ns[kus ds fy, mi;ksx fd;k tkrk gSA VLOOKUP
function approximate match vkSj Exact match dks support djrk gSA ;g function Table dks
ck;sa column esa ,d value dks search djrk gSA vkSj mlds ckn vkids }kjk fn;s x;s Particular
column mlh Row esa og value return djrk gSA
Note:- Default :i ls Table ascending order esa sort gksuk pkfg,A
Syntax:-=VLOOKUP(Lookup_Value,Table_array,Col_index_num,[range_lookup])
Where:-
Value:- Table ds lcls igys column fd value, ftls [kkstuk gSA
Table_array:- og table ftlls bl Value dks izkIr djuk gSA
Page17
Col_index_num:- Table/Range dk og column, ftlls value dks izkIr djuk gSA
True= Approximate Match vkSj False= Exact Match
Page18
