Advance Excel Formulas

 Excel - Advance Formulas:

VLOOKUP
=VLOOKUP(B5,marksheet!$A$3:$O$13,8,FALSE)
HLOOKUP
=HLOOKUP(C21,$B$16:$E$18,2,FALSE)

VLOOKUP/MATCH
=VLOOKUP($A2,Sheet1!$A$2:$Y$9995,MATCH('Vlookup Match'!B$1,Sheet1!$A$1:$Y$1,0),FALSE)

REVERSE LOOKUP
=VLOOKUP(A2,CHOOSE({1,2},Sheet1!$N$1:$N$9995,Sheet1!$G$1:$G$9995),2,FALSE)

IS/VLOOKUP

=IF(ISTEXT(C18),VLOOKUP(C18,B2:C15,2,FALSE),VLOOKUP(C18,A2:C15,3,
FALSE)

IFERROR/VLOOKUP

Discount Calculation
=IFERROR(VLOOKUP(S2,$AD$17:$AG$19,MATCH(H2,$AD$16:$AG$16,0),TRUE),"No Discount")
Discount with Amt.
=IF(ISERROR(S2*T2),S2,ABS(S2*T2-S2))

Status-1
=IF(VLOOKUP(N2,$N$1:$U$9995,8,FALSE)>10000,"Very Good","Work Hard")
Status-2
=IF(ISNA(VLOOKUP(K2,$AA$10:$AA$14,1,FALSE)),"No Record Found","Yes We Have Record")
Status-3
=IF(ISNA(VLOOKUP(K2,$AA$10:$AB$14,2,FALSE)),"No Info",VLOOKUP(K2,$AA$10:$AB$14,2,FALSE))
Status-4
=IF(VLOOKUP(N2,$N$1:$U$9995,8,FALSE)>10000,VLOOKUP(N2,$N$1:$U$9995,8,FALSE)*5%,VLOOKUP(N2,$N$1:$U$9995,8,FALSE)*0%)

INDEX/MATCH
=INDEX(Sheet1!$A$1:$Y$9995,MATCH(index!$A2,Sheet1!$N$1:$N$9995,0),MATCH(index!B$1,Sheet1!$A$1:$Y$1,0))

TIPS AND TRICKS
1. Copy the numbers in down series press Ctrl+D from keyboard

2. Format Cell for value first select cell then press Ctrl+1
Then click on Custom ==> Type Coding:
[<40] POOR;[<80] "GOOD";"VERY GOOD"

3. Insert Photo in Comment Box, First insert comment for cell or Emp_ID
then right click on outline of comment box==>Format Comment==>Color &
Lines==>Fill Effects==>Picture then select any photo

Microsoft Excel Assignments

Question :

1. Calculate the Total
2. Calculate the Average.
3. Calculate the Highest & Lowest Marks in every subjects
4. The Result should be pass if the students get 35 or > than 35 marks in each subject

5. The sheet should be rename as “Mark list of 2005-06”

Solution:

Total : =SUM(B4:D4)
AVERAGE : =AVERAGE(B4:D4)
HIGHEST : =MAX(B4:B12)
LOWEST :  =MIN(B4:B12)

RESULT : =IF(MIN(B2:D2)>=35,”PASS”,”FAIL”)

Question :

1. Calculate the Total & Average sales.
2. The comment should be “Good Sale” if the average sales are above Rs.35000/- otherwise
the comment should be “Improving Sales”.

3. The sheet should be renamed as “Quarterly sales for 95-96”.

Solution:

Total : =SUM(B4:E4)

AVERAGE : =AVERAGE(B4:E4)

COMMENT : =IF(G2>35000,”GOOD SALE”,”IMPROVING SALE”)

Right click on sheet and select the Rename option for rename the sheet

Question :

1. Calculate the Total & Average sales of every salesman.

2. The Commission shall be calculated as 15% of average sale. If the average

sale is less than 4000 then “No commission”.

Solution:

Total : =SUM(B4:D4)

AVERAGE : =AVERAGE(B4:D4)

COMMISSION: =IF(F2>4000,F2*15%,”NO COMMISSION”)

Question :

1. Calculate Total variable expenses.

2. Calculate Gross profit as=Sales-Total variable expenses.

3. Calculate Total fixed expenses.

4. Calculate Profit before tax as = Gross profit – Total fixed expenses

5. Calculate Tax as 20% of profit before tax.

6. Calculate Net profit = Profit before tax – Tax.

Solution:

Total variable expenses : =SUM(B7:B9)

Gross profit : =B4-B10

Total fixed expenses : =SUM(B14:B17)

Profit before tax : =B11-B18

20% TAX : =B19*20%

Net Profit : =B19-B20

Question :

1. Calculate the Interest at 23% on Loan amt. (use absolute reference)

2. Rename the sheet as “Calculation of interest amount”.

Solution:

Interest : =B4*E3(Press F4 function key)

Right click on sheet and select the Rename option for rename the sheet

Question :

1. Calculate the Discount Amt. 20% on Seasonal Sale

2. Calculate the Collection

Solution:

Discount : =C4*F3(Press F4 function key)

Collection : =B4-C4

Question :

1. Calculate HRA as =10% of basic salary

2. Calculate DA as = 15% of basic salary

3. Calculate CONVEYANCE as = 5% of basic salary

4. Calculate Total = BASIC + HRA + DA + CONV.

5. Calculate PF as = 5% of TOTAL

6. Calculate Net Salary = TOTAL-PF

7. Calculate Designation as = if the employees net salary is more than 5000/- his

Designation is Executive otherwise he is a Clerk.

8. Rename the sheet to “Payroll”.

Solution:

HRA : =B4*10%

DA : =B4*15%

CONVEYANCE : =B4*5%

TOTAL : =SUM(B4:E4)

PF : =F4*5%

NET SALARY : =F4-G4

DESIGNATION : =IF(H2>5000,”EXECUTIVE”,”CLERK”)

Question :

Create a Column chart for the above data with details

Solution:

First select the Table from A3 to B10, then insert chart from Insert-2D Column Chart option or from Chart button from Insert Tab. Then give Axis Titles, Legents, Data Labels for chart.

Output

Question :

Sort the table data Name wise, Product wise then apply Sub Total for table and Filters

Solution:

1. Select the table from A3 to D16 then select Sort option from Data Tab =>Sort by=>City then OK

2. Sub Total : Select the table from A3 to D16 then select Subtotal option from Data Tab =>

   At each change in =>City

   Use Function=>Sum 

Add subtotal to =>Sales Value

3. Filter : Click at the header of the table then select Filter option from Data Tab

Question :

Calculate the No. of Days and Fine Status from the above Table

Solution:

No. of Days : =D4-C4

Fine Status : =IF(E2>15,(E2-15)*5,”NO FINE")

Question :

Calculate the Discount Card for above Sales Report

Solution:

=IF(B2>=90000,”GOLD CARD”,IF(B2>=80000,”SILVER CARD”,IF(B2>=65000,

”ALUM CARD”,IF(B2>=50000,”PLATINUM CARD”,”NO CARD"))))

Question :

Copy the the above table in other software like Word, Power Point etc.

Solution:

1. Select the table from A3 to B8 then select Copy option from Home Tab

2. Open Power Point then click on Arrow near Paste option =>Paste Special=>Paste Link=>  Microsoft Excel 2003 Worksheet Object =>ok 

DATA VALIDATION

For Sales Value - Select D2 to D14 range then click on the Data Tab → Data Validation→ Setting

For Date - Select E2 to E14 range then click on the Data Tab → Data Validation→ Setting

For Mobile No. - Select F2 to F14 range then click on the Data Tab → Data Validation→ Setting

For List creation - First type a City names in a new cell to create a new list 

Then  Select B2 to B14 range then click on the Data Tab → Data Validation→ Setting

Finally  Select City from the list

What If Analysis

Goal Seek : To set a target value for any table, click on Data → What if Analysis →

then select Goal Seek option

select the Set Cell-E9, To Value-30000 and By changing-E7 cell shown in below:

Scenario Manager : To show the changing value of a table, click on Data → What if Analysis →

then select Scenario Manager

Click on the Add button to create a Scenario for price

then type Scenario name : Diwali Offer Price and Changing Cell : E6 then click on Ok button

Date and Time Functions

         Date and Time Functions:

1    :    =NOW()

2    :    =TODAY()

3    :    =DATE(2021,7,10)

4    :    =DAYS360(C8,C9,TRUE)

5    :    =YEARFRAC(C8,C9,3)

6    :    =DATEDIF(C12,TODAY( ),"Y")

7    :    =DAY(C3)

8    :    =MONTH(C3)

9    :    =YEAR(C3)

10  :    =HOUR(C3)

11  :    =MINUTE(C3)

12  :    =SECOND(C3)

13  :    =NETWORKDAYS(B21,B22,B23)

14  :    =NETWORKDAYS.INTL(B26,B27,11,B28)