NAME OF FUNCTION Dcount date day days360 today now datevalue Cell If If or If And If not VLOOKUP Hlookup Abs

Sumif Round Square Root Product Roundup int Count Counta Countif Min Max

CATEGORI DATABASE DATE & TIME DATE & TIME DATE & TIME DATE & TIME DATE & TIME DATE & TIME INFORMATION LOGICAL LOGICAL LOGICAL LOGICAL LOOKUP & REFERENCE LOOKUP & REFERENCE MATH & TRIG MATH & TRIG MATH & TRIG MATH & TRIG MATH & TRIG MATH & TRIG MATH & TRIG STATISTICAL STATISTICAL STATISTICAL STATISTICAL STATISTICAL

MARKSHEET0
SR.NO NAME OF STUDENT PHY 1 2 3 4 5 6 7 Ganguly Shewag Sachin Tendulkar Rahul Dravid s Agarkar Kumble 95.00 75.00 80.00 92.00 95.00 97.00 20.00 CHEM 90.00 75.00 70.00 90.00 85.00 81.00 82.00 VLOOKUP Countif Min Max Sumif Lower kumble Abs Int 10 10.00 Decimal part will be ignored by the function 5 20.00 97.00 552 Counta Countablank Columns Column Left Right Char Code 7 Row Rept Round Kumb ble d 49 77 65 65.25 100 7 1 Ramesh 10 6 ramesh Coun esh Maths Chem MATHS 90.00 90.00 70.00 95.00 75.00 30.00 31.00 2 90 Phy 75 Grade 75 B TOTAL 275.00 240.00 220.00 277.00 255.00 208.00 133.00 AVERAGE MINIMUM 91.67 80.00 73.33 92.33 85.00 69.33 44.33 90.00 75.00 70.00 90.00 75.00 30.00 20.00

Upper KUMBLE

KumbleKumbleKumbleKumbleKumbleKumbleKumbleKumbleKumbleKumbl

Upper Lower Proper Char Rept Replace Dollar concatenate Exact PMT PPMT IPMT PV FV NPER RATE SLN

TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT FINANCIAL FINANCIAL FINANCIAL FINANCIAL FINANCIAL FINANCIAL FINANCIAL FINANCIAL
SLN Rs.39,900.00

Square Root Replace Dollar concatenate Exact

5.567764363 AnilKumble $500.00 Anil Kumble FALSE Anil S 123 500

Proper T Len Product sign Round Fixed Cell Mid

Rahul Sharma 4 720 -1 72 100,000.00 G e 200000 500 199500 39900

rahul sharma 123 1000

71.21 100000 6/22/1905 100

MAXMUM GRADE 95.00 90.00 80.00 95.00 95.00 97.00 82.00 A B C A B C D

REMARKS MERIT DIST FIRST MERIT DIST FIRST FAIL

KumbleKumbleKumbleKumbleKumbleKumbleKumbleKumbleKumbleKumble

FINANCIAL FUNCTIONS 1 2 3 4 5 6 7 8 PMT PPMT IPMT PV FV NPER RATE SLN 1 PMT Example : PAYMENT

Suppose you are giving a loan of Rs.200000/= to your friend @16% interest p.a for 2 Years and he will back the money through a monthly installment system. FIND OUT WHAT WILL BE THE INSTALLMENT AMOUNT ??????????? Rs.9,792.62 Here Present Value is assumed negative to indicate that the loan is given, not taken. If positive value supplied as present value, result will be negative value. The above Answere is Monthly Installment On Loan, But it contains Principl+Interest Amount

Ans

2 PPMT Rs.7,125.96 3 IPMT Rs.2,666.67

PRINCIPLE ON PAYMENT

INTEREST ON PAYMENT

FIND OUT THE BREAK-UPS OF THE ABOVE INSTALLMENT AMOUNT FOR 2 YEARS WHAT WOULD BE THE INTEREST AMOUNT OF 5TH INSTALMENT?

INSTALMENTS

PMT Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62

1 2 3 4

PPMT IPMT Rs.7,125.96 Rs.2,666.67 Rs.7,220.97 Rs.2,626.65 Rs.7,317.25 Rs.2,586.10 Rs.7,414.81 Rs.2,545.00

5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.9,792.62 Rs.235,022.93

Rs.7,513.68 Rs.7,613.86 Rs.7,715.38 Rs.7,818.25 Rs.7,922.49 Rs.8,028.12 Rs.8,135.17 Rs.8,243.63 Rs.8,353.55 Rs.8,464.93 Rs.8,577.80 Rs.8,692.17 Rs.8,808.06 Rs.8,925.50 Rs.9,044.51 Rs.9,165.10 Rs.9,287.30 Rs.9,411.14 Rs.9,536.62 Rs.9,663.77 Rs.200,000.00

Rs.2,503.36 Rs.2,461.17 Rs.2,418.41 Rs.2,375.08 Rs.2,331.17 Rs.2,286.68 Rs.2,241.60 Rs.2,195.91 Rs.2,149.61 Rs.2,102.70 Rs.2,055.16 Rs.2,006.99 Rs.1,958.18 Rs.1,908.71 Rs.1,858.59 Rs.1,807.80 Rs.1,756.33 Rs.1,704.17 Rs.1,651.32 Rs.1,597.76 Rs.51,795.11

4 PV Example :

Present Or Principle Value of an Investment.

Suppose A person deposited certain amount in a bank @12% Interest p.a in such a way that after Five years he will get total sum of Rs.50000 to get his Further Education. FIND OUT THE AMOUNT OF DEPOSIT ??????????? Rs.28,371.34 5 FV Future Value of an Investment

Ans

Example :

Ans

Suppose you want to deposit Rs.2000/= each month in a bank for 1 Year and scheme will earn 12% interest rate. RECURRING DEPOSIT SCHEME 2nd Option is You deposit Rs.25,000/= in a bank for 1 Year and rate of interest is 12% P.A. FIND OUT THE AMOUNT AT THE END OF MATURITY ??????????? WHICH SCHEME IS PROFITABLE TO YOU?????????? Rs.25,365.01 R.D LOSS Rs.28,000.00 F.D PROFITABLE Rs.2,634.99 Rs.25,365.01

6 NPER 12.00 (As per Above example) FIND OUT THE NO OF PERIODS ??????????? 7 RATE Example : Rate of Interest

To Calculate the rate of four year Rs.18,000/= Loan with monthly payments of Rs.500 FIND OUT THE RATE OF INTEREST ??????????? 1.24% MONTHLY 14.90% YEARLY

Ans

8 SLN Example

Straight Line Depreciation

Cost of Asset is 200000,Salvage is 500,Life of Asset is 5 Years,Find out the dep on SLN Method? Rs.39,900.00

Rs.9,792.62 Rs.7,125.96 Rs.2,666.67 Rs.4,084,707.81 Rs.1,027,024.60

t p.a for 2 Years and he will

he loan is given, not taken. negative value.

pl+Interest Amount

OUNT FOR

nvestment. such a way that

nd scheme will earn

erest is 12% P.A.

dep on SLN Method?

EMP.CODE E001 E002 E003 E004 E005 E006 E007 E008 E009 8 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

EMPLOYEE NAME Ganguly Shewag Sachin Tendulkar Rahul Dravid Ashish Nehra Agarkar Srinath Kambli Kumble Cell Abs Sumif Round Square Root Product Roundup int Count Counta Countif Min Max Upper Lower Proper Char Rept Replace Dollar concatenate Exact

DEPT. Mkt Fin Mkt Fin Admin Hrd Fin Admin Hrd INFORMATION MATH & TRIG MATH & TRIG MATH & TRIG MATH & TRIG MATH & TRIG MATH & TRIG MATH & TRIG STATISTICAL STATISTICAL STATISTICAL STATISTICAL STATISTICAL TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT

DESGN Manager Officer Clerk Clerk Manager Accountant HRD HRD Accountant
,2 25 30000 7,500.00 97.46794345 240000 7,501.00 7,500.00 9 9 3 4,500.00 15,000.00 KUMBLE kumble

BASIC 15,000.00 12,000.00 4,500.00 4,500.00 15,000.00 7,500.00 9,500.00 9,500.00 7,500.10

2500.23 2500 9

Rahul Sharma RAHUL SHARMA $ ANIL SHARMAANIL $7,500.10 OVER DUE DAYS BY45 TRUE 25 36 SHARMA KumbleKumbleKumbleKumble

E001 E002 E003 E004 E005 E006 E007 E008 E009

EMP.CODE NAME DEPT. EMPLOYEE Ganguly Mkt Shewag Fin Sachin Tendulkar Mkt Rahul Dravid Fin Ashish Nehra Admin Agarkar Hrd Srinath Fin Kambli Admin Kumble Hrd

DESGN BASIC Manager 15,000.00 Officer 12,000.00 Clerk 4,500.00 Clerk 4,500.00 Manager 15,000.00 Accountant 7,500.00 HRD 9,500.00 HRD 9,500.00 Accountant 7,500.10

code NAME #N/A

$155.00 PRASHANT PASHTE Prashant Pashte

155

Prashant PashtePrashant PashtePrashant PashtePrashant PashtePrashant Pashte

OVER DUE DAYS BY 25

45 FALSE

26

LIST OF FUNCTIONS

SR.NO 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

NAME OF FUNCTION Dcount date day days360 today now datevalue Cell If If or If And If not VLOOKUP Hlookup Abs Sumif Round Square Root Product Roundup int Count Counta Countif Min Max Upper Lower Proper Char Rept Replace Dollar concatenate Exact PMT PPMT

CATEGORI DATABASE DATE & TIME DATE & TIME DATE & TIME DATE & TIME DATE & TIME DATE & TIME INFORMATION LOGICAL LOGICAL LOGICAL LOGICAL LOOKUP & REFERENCE LOOKUP & REFERENCE MATH & TRIG MATH & TRIG MATH & TRIG MATH & TRIG MATH & TRIG MATH & TRIG MATH & TRIG STATISTICAL STATISTICAL STATISTICAL STATISTICAL STATISTICAL TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT FINANCIAL FINANCIAL

38 39 40 41 42 43

IPMT PV FV NPER RATE SLN

FINANCIAL FINANCIAL FINANCIAL FINANCIAL FINANCIAL FINANCIAL

Relative Cell Relative Column Relative Row

A1,B6 RC RR

Constant Cell Absolute Column Absolute Row

$B$1 AC AR

Constant Column $B1 Absolute Column AC Relative Row RR

Constant Row Relative Column Absolute Row

B$1 RC AR

SR.NO

NAME OF STUDENT

PHY

CHEM >80

MATHS

TOTAL

SR.NO 1 2 3 4 5 6 7

NAME OF STUDENT Ganguly Shewag Sachin Tendulkar Rahul Dravid s Agarkar Kumble

PHY CHEM 95.00 80.00 80.00 92.00 95.00 97.00 20.00

MATHS 80.00 82.00 80.00 90.00 85.00 81.00 82.00 90.00 90.00 70.00 95.00 75.00 30.00 31.00

TOTAL 265.00 252.00 230.00 277.00 255.00 208.00 133.00

DCOUNT

SHORTCUT KEYS Insert Sheet = Shift + F11 Q Ctrl + 1 = Font Ctrl + 2 = Bold Ctrl + 3 = Italics Ctrl + 4 = Underline Ctrl + 5 = Strike Through Ctrl + F3 = Define Name Ctrl + F9 = Minimize Ctrl + F10 = Maximize Ctrl + W or Ctrl + F4 = Close File Clt+Shift+P Increase the Font Size Clt+Shift+f Change the Font Clt+: Current Date Clt+" Current Time Clt+' Copy Formula Clt+Shift+" Copy Formula as Text Constant F4 Constant All Select Cell +F4 Alt+Enter Wrap Text Windows +D Minimise All Applicaions Clt+9 hide row Clt+Shift+9 Unhide row Clt+0 hide coloumn Clt+Shift+0 Unhide coloumn Clt+Shift+pageup selet more than one sheet Clt+Shift+pageup selet more than one sheet Clt+7 show standard toolbars Clt+F3 Define name to u r database Clt+Shift+7 Boarder Clt+Shift+Remove Boarder DATE FUNCTIONS DATE DAY MONTH 26 1/26/2008 DAY Q A DAYS360 Q A Q A Q A TODAY 5/4/2012 NOW 5/4/2012 1:26 1/1/2008 1/31/2008 30 2/15/2008 15

YEAR 1 2008

Q A A

DATEVALUE #VALUE! #VALUE!

AVERAGE

MINIMUM MAXMUM GRADE

REMARKS CHEM DIST <85 REMARKS DIST DIST DIST MERIT DIST FIRST FAIL

AVERAGE 88.33 84.00 76.67 92.33 85.00 69.33 44.33

MINIMUM MAXMUM GRADE 80.00 95.00 B 80.00 B 70.00 80.00 B 90.00 95.00 A 75.00 95.00 B 30.00 97.00 C 20.00 82.00 D

1/29/2008 3

LOGICAL FUNCTIONS

CALCULATE "HRA" ON THE FOLLOWING CONDITIONS :1 Basic is More Than Rs.5000 @5% IF 2 Basic is Less Than Rs.5000 @10% Name Ramesh Rajesh Raghu Rajesh Rashon Basic 4500 9000 3000 12000 7000 Dept HRD MKT ADMI MKT HRD Hra 450 450 300 600 350 Remarks Less Thane 5000 More than 5000 Less Thane 5000 More than 5000 More than 5000

Sr.No 1 2 3 4 5 B

CALCULATE "HRA" ON BASIC ON THE FOLLOWING CONDITIONS :HRD 10% MKT 15% OHTERS 25% Name Ramesh Rajesh Raghu Rajesh Rashon Basic 4500 9000 3000 12000 7000 Dept HRD MKT ADMIN MKT HRD Hra 450 1350 750 1800 700 Remarks

Sr.No 1 2 3 4 5

IF AND A CALCULATE HRA @50% IF EMPLOYEE'S DON'T HAVE HOUSE AND IF EMPLOYEE'S BASIC SALARY IS LESS THAN RS.10,000, OHTHER WISE "0". Name Ramesh Rajesh Raghu Rajesh Rashon Basic House 4500 FALSE 9000 TRUE 3000 FALSE 12000 FALSE 7000 TRUE IF OR A CALCULATE BOUNS @50% IF EMPLOYEE'S SALARY IS LESS THAN OF BASIC RS.7500 OR IF EMPLOYEE'S YEAR OF SERVIES [YOS] IS MORE THAN OR EQUAL TO 5 YEARS,OTHER WISE "0" Name Ramesh Basic YOS 4500 BONUS 2 2250 one or both condition to be apply Remarks Hra 2250 0 1500 0 0 Remarks

Ecode 1 2 3 4 5

Both condition must be apply

Ecode 1

2 3 4 5

Rajesh Raghu Rajesh Rashon

9000 3000 12000 7000

3 2 3 5

0 1500 one or both condition to be apply 0 3500

A IF NOT (<>) CACULATE HRA @10% OTHER THAN "MKT" Dept, MKT IT WILL BE @25% Name Ramesh Rajesh Raghu Rajesh Rashon Basic 4500 9000 3000 12000 7000 Dept HRD MKT ADMI MKT HRD Hra 450 2250 300 3000 700 <> 450 2250 300 3000 700 Remarks

1 2 3 4 5

RS,OTHER WISE "0"

EMP.CODE
E007

EMPLOYEE NAME
Srinath Fin

DEPT.
HRD

DESGN

BASIC
9500

EMP.CODE E001 E002 E003 E004 E005 E006 E007 E008 E009 code name dept desgn basic

EMPLOYEE NAME Ganguly Shewag Sachin Tendulkar Rahul Dravid Ashish Nehra Agarkar Srinath Kambli Kumble E003
Sachin Tendulkar Mkt Clerk 4500

DEPT. Mkt Fin Mkt Fin Admin Hrd Fin Admin Hrd E007
Srinath Fin HRD 9500

DESGN Manager Officer Clerk Clerk Manager Accountant HRD HRD Accountant E006
Agarkar Hrd Accountant 7500

BASIC 15,000.00 12,000.00 4,500.00 4,500.00 15,000.00 7,500.00 9,500.00 9,500.00 7,500.10 EMP.CODE EMPLOYEE NAME

EMP.CODE EMPLOYEE NAME DEPT. DESGN BASIC CODE NAME DEPT DESGN BASIC

E001 E002 E003 E004 E005 Ganguly Shewag Sachin Tendulkar Dravid Rahul Ashish Nehra Mkt Fin Mkt Fin Admin Manager Officer Clerk Clerk Manager 15,000.00 12,000.00 4,500.00 4,500.00 15,000.00 E005
Ashish Nehra Admin Manager 15000

E006 E007 Agarkar Srinath Hrd Fin Accountant HRD 7,500.00 9,500.00

E008 E009 Kambli Kumble Admin Hrd HRD Accountant 9,500.00 7,500.10

VLOOKUP

EMP.CODE NAME DEPT. EMPLOYEE E001 Ganguly Mkt E002 Shewag Fin E003 Sachin Tendulkar Mkt E004 Rahul Dravid Fin E005 Ashish Nehra Admin E006 Agarkar Hrd E007 Srinath Fin E008 Kambli Admin E009 Kumble Hrd EMP.CODE EMPLOYEE NAME DEPT. DESGN BASIC

DESGN BASIC Manager 15,000.00 EMP.CODE Officer 12,000.00EMPLOYEE NAME Clerk 4,500.00 DEPT. Clerk 4,500.00 DESGN Manager 15,000.00 BASIC Accountant 7,500.00 HRD 9,500.00 HRD 9,500.00 Accountant 7,500.10 EMP.CODE
E003

E001 Ganguly Mkt Manager 15000

HLOOKUP

E001 E002 E003 E004 E005 Ganguly Shewag Sachin Tendulkar Dravid Ashish Nehra Rahul Mkt Fin Mkt Fin Admin Manager Officer Clerk Clerk Manager 15,000.00 12,000.00 4,500.00 4,500.00 15,000.00

E006 E007 Agarkar Srinath Hrd Fin Accountant HRD 7,500.00 9,500.00

EMPLOYEE NAME
Sachin Tendulkar Mkt

DEPT.

DESGN
Clerk

BASIC
4500

E008 E009 Kambli Kumble Admin Hrd HRD Accountant 9,500.00 7,500.10