Advance Excel Notes

MARK SHEET DAILY SALES REPORT SALARY CHART ATTENDENCE SHEET


ELECTRICITY BILL HOSPITAL BILL PRODUCTION REPORT HLOOKUP,VLOOKUP

AD.VLOOKUP VLOOKUP IN 2 SHEET PIVOT TABLE AD. FILTER

DATE WISE FILTER CONSOLIDATE TABLE LIST

COUNTIF DATA TABLE PMT , IPMT
FINANCIAL , PPMT
FORMULAS CONDITIONAL FORMATTING



DATA VALIDATION HYPERLINK GOAL SEEK AD.GOAL SEEK

DATA ENRTY (FORM) PROFIT & LOSS (ACC) BIRTH CALCULATOR HOLIDAY PACKAGE

FINANCE SHEET MATH FORMULAS OFFICE WORK SCHEDULE DAY & NIGHT SHIFT


IFERROR SUMIF PROGRES
PROGRES BAR &BAR
MATH

, A B C D E F G H I J K L M N O
1
2 MARK SHEET
3 S.NO STUDENT NAME ROLL NO CLASS HINDI MATHS ENGLISH SCIENCE S.S.T TOTAL PERCENTAGE RESULT DIVISION MAXIMUM MINIMUM
4 1 NEHA 11 10TH 74 84 62 64 55 339 67.8 PASS SECOND 84 55
5 2 NEERAJ 12 10TH 85 58 53 52 45 293 58.6 PASS SECOND 85 45
6 3 NISHA 13 10TH 65 55 54 65 65 304 60.8 PASS SECOND 65 54
7 4 DEEPAK 14 10TH 74 65 65 45 74 323 64.6 PASS SECOND 74 45
8
9
10 FORMULAS MARK SHEET
11 TOTAL SUM(SELECT TO START SUBJCET COLUNM NUMBER : SELECT TO END SUBJECT COLUNM NUMBER)
12 PERCENTAGE SELECT TO PERCENTAGE COLUNM NUMBER / TOTAL SUBJECT EXP.(J2/5)
13 RESULT IF(SELECT TO PERCENTAGE COLUNM NUMBER>=33,"PASS","FAIL")
14 DIVISION IF(SELECT TO PERCENTAGE COLUNM NUMBER>=70,"FIRST",IF(SELECT TO PERCENTAGE COLUNM NUMBER>=50,"SECOND",IF(SELECT
15 TO PERCENTAGE COLUNM NUMBER>=33,"THIRD","FAIL")))
16 MAXIMUM MAX(SELECT TO START SUBJCET COLUNM NUMBER : SELECT TO END SUBJECT COLUNM NUMBER)
17 MINIMUM MIN(SELECT TO START SUBJCET COLUNM NUMBER : SELECT TO END SUBJECT COLUNM NUMBER)

, A B C D E F G H I
1
2 DAILY SALES REPORT
3 S.NO ITEM NAME PRICE SOLD QUANTITY GROSS AMOUNT WAGES 2% GST 12% DISCOUNT 5% NET PAYABLE AMOUNT
4 1 REDMI 15998 5 79990 1600 9599 4000 87189
5 2 REALME 13899 4 55596 1112 6672 2780 60600
6 3 OPPO 10000 6 60000 1200 7200 3000 65400
7 4 VIVO 12450 4 49800 996 5976 2490 54282
8
9

10 FORMULAS DAILY SALES REPORT
11 GROSS AMOUNT PRICE * SOLD QUANTITY
12 WAGES 2% SELECT TO GROSS AMOUNT * 2%
13 GST 12% SELECT TO GROSS AMOUNT * 12%
14 DISCOUNT 5% SELECT TO GROSS AMOUNT * 5%
15 NET PAY AMOUNT GROSS AMOUNT + WAGES + GST - DISCOUNT

, A B C D E F G H I J K
1
2 SALARY CHART
3 S.NO EMP.NAME POST BASIC SALARY HRA 5% DA 6% TA 7% GROSS SALARY PF 18% INCOME TAX 12% NET SALARY
4 1 MR. SHYAM ACCOUNTANT 45000 2250 2700 3150 53100 9558 6372 37170
5 2 MR. SHIVAM MANAGER 75000 3750 4500 5250 88500 15930 10620 61950
6 3 MR. REHAN SALESMEN 18000 900 1080 1260 21240 3823.2 2548.8 14868
7 4 MR. NEERAJ PEON 10000 500 600 700 11800 2124 1416 8260
8
9
10 FORMULAS SALARY CHART
11 HRA :- HOUSE RENTAL ALLOWANCE
12 HRA 5% SELECT TO BASIC SALARY COLUNM*5% DA :- DEARNESS ALLOWANCE
13 DA 6% SECLECT TO BASIC SALARY COLUNM*6% TA :- TRAVELLING ALLOWANCE
14 TA 7% SELECT TO BASIC SALARY COLUNM*7% PF :- PROVIDENT FUND
15 GROSS SALARY SELECT TO BASIC SALARY + HRA5% + DA6% + TA7% IT :- INCOME TAX
16 PF 18% SELECT TO GROSS SALARY * 18%
17 INCOME TAX 12% SELECT TO GROSS SALARY * 12%
18 NET SALARY SELECT TO GROSS SALARY - PF 18% - INCOME TAX 12%