1
EXCEL FORMULAS
1.TOTAL
=SUM( SELECT SUBJECT MARKS ) ENTER
2.AVERAGE
=AVERAGE ( SELECT SUBJECT MARKS ) ENTER
3.MAXIMUM
=MAX ( SELECT SUBJECT MARKS ) ENTER
4.MINIMUM
=MIN ( SELECT SUBJECT MARKS ) ENTER
5.COUNT (NUMBERS ONLY)
=COUNT ( SELECT SUBJECT MARKS ) ENTER
6.COUNTA (CHARACTERS WITH NUMBERS)
=COUNTA ( RANGE ) ENTER
7.COUNT BLANK (BLANK SPACE ONLY)
=COUNTBLANK ( RANGE) ENTER
8.COUNTIF (PARTICULAR NAME OR NUMBER)
=COUNTIF ( RANGE , “ARUN” ) ENTER
=COUNTIF ( RANGE ,”85” ) ENTER
9.COUNTIF (COUNT 85 AND ABOVE)
=COUNTIF ( RANGE , “>=85” ) ENTER
10.COUNTIF (COUNT 85 AND BELOW)
=COUNTIF ( RANGE , “<=85” ) ENTER
11.COUNTIF (COUNT NAME BEGINNINGONLY)
=COUNTIF ( RANGE , “ARUN*” ) ENTER
12.COUNTIF (COUNT NAME IN ANY PLACE)
=COUNTIF ( RANGE , “*ARUN*” ) ENTER
13.TRANSPOSE
=TRANSPOSE ( RANGE ) CTRL+SHIFT=ENTER
14.ROUND
=ROUND ( RANGE , 2 ) ENTER
2
15.UPPERCASE
=UPPER ( SELECT WORD ) ENTER
16.LOWERCASE
=LOWER (SELECT WORD ) ENTER
17.PROPERCASE
=PROPER ( SELECT WORD ) ENTER
18.DATEDIF
12/08/1985 02/10/1999
=DATEDIF ( SELECT 1ST DATE , SELECT 2ND DATE , “D” ) ENTER
=DATEDIF ( SELECT 1ST DATE , SELECT 2ND DATE , “Y” ) ENTER
=DATEDIF ( SELECT 1ST DATE , SELECT 2ND DATE , “M” ) ENTER
19.CONCATENATE
NAME1-MUKI NAME2-LAN
=CONCATENATE ( SELECT NAME1 , SELECT NAME2 ) ENTER
20.AGE CALCULATION
=DATEDIF ( SELECT DATE , TODAY() , “Y” ) ENTER
=DATEDIF ( SELECT DATE , TODAY() , “YM” ) ENTER
=DATEDIF ( SELECT DATE , TODAY() , “MD” ) ENTER
21.TODAY
=TODAY() ENTER
22.NOW()
=NOW() ENTER
23.ROMAN
=ROMAN ( SELECT NUMBER ) ENTER
24.DOLLAR
=DOLLAR() ENTER
25.GRADE
=IF ( AND ( SELECT AVERAGE MARKS >=90 ) , “A GRADE” ,
IF ( AND ( SELECT AVERAGE MARKS >=80 ) , “B GRADE”) )
3
26.RESULT
=IF ( AND ( SELECT 1ST SUBJECT MARK >=35 , SELECT 2ND SUBJECT MARK >=35 , SELECT 3RD
SUBJECT MARK >=35 , SELECT 4TH SUBJECT MARK >=35 , SELECT 5TH SUBJECT MARK >=35 ) , “PASS”
, “FAIL” ) ENTER
27.RANK
=IF ( RANGE , “PASS” , RANK (0,D) , “NO RANK” )
28.LEFT
=LEFT ( SELECT WORD , 4 ) ENTER
29.RIGHT
=RIGHT ( SELECT WORD , 4 ) ENTER
30.TEXT LENGTH
=LEN (SELECT WORD ) ENTER
31.MID
=MID ( SELECT WORD , 4 , 3 ) ENTER
32.EXACT
=EXACT ( SELECT 1ST WORD , SELECT 2ND WORD ) ENTER
33.REPEAT
=REPT ( “JOE” , “15” ) ENTER
34.TRIM
=TRIM ( SELECT SPACING WORD ) ENTER
35.FIND DATE DAY
=TEXT (“7/038/2014” , “DDDD” ) ENTER
36.FACT
=FACT (5) ENTER
37.TO PRINT NUMBERS ONLY
=IFERRROR ( A2* , “” )
38.IF
ITEMS SALES TARGET
APPLE 00 50
GRAPES 50 20
BANANA 75 60
CHERRY 20 30
=IF ( SELECT SALES>SELECT TARGET , “COMPLETE” , “NOT COMPLETE” )
4
39.SUBSTITUTE
=SUBSTITUTE ( SELECT WORD , SELECT MI8STAKE WORD , SELECT CORRECTION WORD ) ENTER
40.VLOOK UP
=VLOOK UP ( SELECT RANGE , SELECT DATA , COLUMN NUMBER , 0 ) ENTER
41.HLOOK UP
=HLOOK UP ( SELECT RANGE , SELECT DATA , ROW NUMBER , 0 ) ENTER
42.YEAR (06/23/2012)
=YEAR ( SELECT DATE ) ENTER
43.ADD DAYS (06/23/2012)
=SELECT DATE + 5 ENTER
44.(06/23/2012)
=DATE ( YEAR ( SELECT DATE )+4 , MONTH ( SELECT DATE )+2 , DAY ( SELECT DATE )+9 ) ENTER
45.HOUR
=HOUR ( SELECT TIME ) ENTER
46.MINUTE
=MINUTE ( SELECT TIME ) ENTER
47.TEXT (“25 MAY 15” , “DDDD” )
48.TO APPLY BULLETS
2 TAMIL NADU
3 KERALA
4 ANDHRA
5 ODISHA
=CHAR (149) & A2
49.TO APPLY ROW AND COLUMN FORMULA
BEFORE SELECT RANGE
=ROW()*COLUMN() CTRL+ENTER
5
50.TO APPPLY RESULT PASS-GREEN , FAIL-RED
GO TO FORMAT CELL PRESS CTRL+1
CUSTOM[GREEN][>35]’PASS’ , [RED] ‘FAIL’
51.TO APPLY PHONE NUMBER +91 PREFIX
GO TO FORMAT CELL PRESS CTRL+1
CUSTOM TYPE “+91” #
RESULT: +91 9884077663
52.TO REMOVE AFTER SPACE WORDS
EG:ARUN KUMAR
PRESS CTRL+1 REPLACE
FIND WHAT SPACE *
REPLACE WITH NOT VALUE
PRESS REPLACE ALL
RESULT: ARUN
53.TO APPLY OTY BEFORE “KG” AND RATE BEFORE “RS.”
GO TO FORMAT CELL PRESS CTRL+1
BEFORE SELECT OTY RANGE
CUSTOM 0”KG”
BEFORE SELECT RATE RANGE
CUSTOM “RS.”0
6
