Showing posts with label data validation. Show all posts
Showing posts with label data validation. Show all posts

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