Showing posts with label excel assignments. Show all posts
Showing posts with label excel assignments. Show all posts

Saturday, January 15, 2022

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

Monday, March 9, 2020

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)

 

Advance Excel Formulas