Excel Assignment 01

Assignment on Basic Calculation & SUM Function

Order ID Product Unit Price Quantity Discount Revenue Tax (2 %)

10259 Retail goods 8 10 5% 76 1.52
10259 Amazon Prime 20 1 0% 20 0.4
10260 Consumer electronics 7 16 25% 84 1.68
10260 Digital content 15 50 0% 750 15
10260 Amazon Games 39 15 25% 438.75 8.775
10260 Amazon Art 12 21 25% 189 3.78
10261 Amazon Video 8 20 0% 160 3.2
10261 Amazon Business 14 20 0% 280 5.6
10262 Amazon Drive 17 12 20% 163.2 3.264
Total: 2160.95 43.219

Write Formulae to Find the Following Points.

1. Calculate the revenue and tax on the revenue for each product
2. Calculate the net income of each product
3. Calculate the total revenue of all products
4. Calculate & Check Your Total should match with Given Total
Net income
74.48
19.6
82.32
735
429.975
185.22
156.8
274.4
159.936
2117.731
Excel Assignment 03 Assignment on Basic Formulae Calculation, Using SUM, RANK, MIN, MAX & AVER

Profit Rate 15%

Ticket Ticket Ticket Remaining

Room Price Available Sold Tickets %Unsold Earning
Bahubali
2 38 20 18 47.4 $ -
Rahees $ 250.00 37 15 22 59.5 $ 3,750.00

Boss Baby $ 650.00 76 43 33 43.4 $ 27,950.00

Airlift $ 200.00 46 16 30 65.2 $ 3,200.00
Rustom $ 250.00 47 30 17 36.2 $ 7,500.00
Sairat $ 150.00 47 40 7 14.9 $ 6,000.00
Pink $ 250.00 75 32 43 57.3 $ 8,000.00
Spiderma
n $ 340.00 23 12 11 47.8 $ 4,080.00
X-Man $ 430.00 67 15 52 77.6 $ 6,450.00

Final
Destinatio
n $ 670.00 37 17 20 54.1 $ 11,390.00
Total $ 3,190.00 493 240

Average
Ticket Price $ 354.44
Cheapest
Ticket $ 150.00

Most
Expensive
Ticket $ 3,190.00
UM, RANK, MIN, MAX & AVERAGE Function

Profit Value Rank

$ - 10
$ 562.50 8

$ 4,192.50 1
$ 480.00 9
$ 1,125.00 4
$ 900.00 6
$ 1,200.00 3

$ 612.00 7
$ 967.50 5

$ 1,708.50 2
 Income & Tax Structure in IPL Players
Name Of the Players Origin IPL Income
Virat Kohli Indian Player 50000000
Ben Strokes Overseas Player 60000000
Shikhar Dhawan Indian Player 36000000
MS Dhoni Indian Player 46000000
Tymal Mills Overseas Player 27000000
Rohit Sharma Indian Player 42000000
Gautam Gambhir Indian Player 27000000
Kieron Pollard Overseas Player 47000000
AB de Villiers Overseas Player 18000000
Suresh Raina Indian Player 25000000

Create an Excel Sheet as Shown in the Figure and Try to Find TD

1. Calculate TDS Levied, on the Following Basis
a. 10% TDS Levied on Indian Player
b. 20% TDS Levied on Overseas Player
2. Net Income is Calculated After Deducting TDS Amo
 Excel Assignment 02

Assignment on IF Function & Nested IF Problem

ucture in IPL Players

TDS-TAX TDS Amount NET Income
10% 5000000 45000000
20% 12000000 48000000
10% 3600000 32400000
10% 4600000 41400000
20% 5400000 21600000
10% 4200000 37800000
10% 2700000 24300000
20% 9400000 37600000
20% 3600000 14400000
10% 2500000 22500000
0 0
n the Figure and Try to Find TDS & Net Income
on the Following Basis
ied on Indian Player
ied on Overseas Player
ated After Deducting TDS Amount From IPL Income
Problem No.2
Create an Excel Sheet as Shown in the Figure and Try Finding Commission based on following Condition

Name Of Salesman Sales Commission

Virat Kohli 90000 8%
MS Dhoni 70000
Sachin Tendulkar 35000
Yuvraj Singh 18000

Sales Commission
0 – 40000 5%
40000 – 80000 7%
80000-120000 8%
120000-150000 10%
150000-180000 12%
180000-200000 15%
>200000 18%
 Create a Worksheet as Show
ssion based on following Condition 1. Remaining Tick
2. Even Find Aver
Excel Assignment 04 Assignment on Basic Formulae Calculatio

Salary Calculation
Name Basic Pay Dept DA
Salman Khan 50000000 Actor 10%
Amir Khan 60000000 Actor 10%
Arijit Singh 36000000 Singer 10%
Sonu Nigam 46000000 Singer 10%
Ranveer Singh 27000000 Actor 10%
Priyanka Chopra 42000000 Actress 10%
Amitabh Bachchan 27000000 Actor 10%
Deepika Padukone 47000000 Actress 10%
Shaheed Kapoor 18000000 Actor 10%
Ranbir Kapoor 25000000 Actor 10%

Calculate & Find DA, PF, Spl. Allowance & Gross From Above Shown Worksheet Bas
1. DA is 18% of Basic Pay, if BASIC PAY is GREATER THEN 2 Crores Else 10%
2. PF is 12% of the Basic Pay
3. Spl. Allowance is Given ONLY to ACTRESS @ 10% of Basic Pay
Gross is Calculated on the Basis of Basic Pay + DA – PF + Allowance
ate a Worksheet as Shown in the Figure Above, & Find
1. Remaining Tickets, % Unsold, Earning, Profit Value (If Profit Rate is 15%) & Rank.
2. Even Find Average Price, Cheapest Ticket & Most Expensive Tickets
asic Formulae Calculation & Using IF Function

y Calculation
PF Spl Allowance Gross
6000000 0 44000000.1
7200000 0 52800000.1
4320000 0 31680000.1
5520000 0 40480000.1
3240000 0 23760000.1
5040000 4200000 41160000.1
3240000 0 23760000.1
5640000 4700000 46060000.1
2160000 0 15840000.1
3000000 0 22000000.1

Shown Worksheet Based on Following Criteria

THEN 2 Crores Else 10% of Basic Pay

% of Basic Pay
Name Basic Pay Dept DA PF Spl Allowance
Salman Khan 50000000 Actor 18% 6000000 0
Amir Khan 60000000 Actor 18% 7200000 0
Arijit Singh 36000000 Singer 18% 4320000 0
Sonu Nigam 46000000 Singer 18% 5520000 0
Ranveer Singh 27000000 Actor 18% 3240000 0
Priyanka Chopra 42000000 Actress 18% 5040000 4200000
Amitabh Bachchan 27000000 Actor 18% 3240000 0
Deepika Padukone 47000000 Actress 18% 5640000 4700000
Shaheed Kapoor 18000000 Actor 10% 2160000 0
Ranbir Kapoor 25000000 Actor 18% 3000000 0
Gross
44000000.18
52800000.18
31680000.18
40480000.18
23760000.18
41160000.18
23760000.18
46060000.18
15840000.1
22000000.18
if cell equals red

COLOR PRICE FLAG

red 100 x
GREEN 80
red 100 x =IF(D5="red","x","")
blue 90
red 100 x
blue 90
ed","x","")
if cell not blank

task completed status

plan finalized 3-Aug close
get city permit 3-Aug close
begin demoltion 9-Aug close =IF(B4="","OPEN","CLOSE")
oder new appliance open
new gas line installed 5-Sep close
oder new lights open
cabinets odered 9-Sep close
open
task completed close
plan finalized 1-Aug close
get city permit 3-Aug close
begin demoltion 9-Aug close
oder new appliance open
new gas line installed 5-Sep close
oder new lights open
cabinets odered 9-Sep close
EN","CLOSE")
invoice status with nested if

invoice due date amount amount paid open blanced status

34628 4-Sep-24 5000 5000 2500 overdue
34676 11-Jun-22 0 0 1000 overdue
34724 25-Mar-16 5000 5000 2500 overdue
34772 10-Apr-16 10,000 10000 0 overdue
34820 10-Jun-16 5000 5000 1500 overdue
34868 18-Jun-16 10,000 50 10000 overdue

=IF(E4=0,"paid",IF(B4<TODAY(),"overdue","open"))

invoice due date amount amount paid open blanced status

34628 1-Feb-16 5000 5000 0 paid
34676 5-Mar-16 0 0 3500 over
34724 25-Mar-16 5000 5000 2100 OVER
34772 10-Apr-16 10,000 10000 0 PAID
34820 10-Jun-16 5000 5000 1500 OVER
34868 18-Jun-16 10,000 50 10000 OVER

=IF(E20=0,"PAID",IF(TODAY()<B20,"OP","OVER"))
 11/4/2024
11/4/2024

11/4/2024

11/4/2024
en"))

))
tax rate calculation with fixed base

amount tax amt tax rate 20%

1200 20% 240 base tax $200
900 10% 90 base limit $1,000
1100 20% 220
1200 20% 240
2000 20% 400

=IF(A4<1000,"10%","20%") =A4*B4
sales men yearly sales year experience salary Quota met bonus
bob 25,000 1 $40,000 NO NA
joe 38,000 5 $55,000 YES
saun 2,500 3 $47,500 NO
sales target 30,000
bonus

exp.<5 year 5 5%
exp.>=5 year 5+ 20% =IF(B3>$C$6,"YES","NO")

=IF(E3="YES",IF(C3<$B$9,$C$9,$C$10)*D3,"NA
 Quota met YES bonus
exp.<5 year 5%
exp.>=5 year 20%

6,"YES","NO")

,$C$9,$C$10)*D3,"NA")
 Excel Assignment 04
Assignment on using AND Function with IF Function

Work Experience
SrNo Candidate Name Field Qualification (In Years) Remark
1 Vijay Chauhan Science Graduate 2 TRUE
2 Pranit Shah Commerce HSC 0.5
3 Nilesh Jha Commerce Graduate 3
4 Komal Sharma Science Graduate 1
5 Ritesh Delve Arts SSC 1

Problem 1
Write Formula to Find the Following Points.
1. Remark Can be “ Eligible” or “ Not Eligible” based on following criteria
a. Criteria 1 : Field Should be Commerce
b. Criteria 2 : Qualification Should be Graduate
c. Criteria 3 : Work Experience Should be Minimum 1 Year
If all the 3 Criteria are Satisfied Remark Will be Eligible Or else Not Eligible.
Problem 2
1. Based on the above table ,Copy the Whole Table and Convert as shown below

SrNo Candid Field QualificWork ExRemark

1 Vijay Chau Science Gratuate 2
2 Pranit Sha CommerceHSC 0.5
3 Nilesh Jha CommerceGraduate 3
4 Komal Sha Science Graduate 1 SrNo
5 Ritesh Del Arts SSC 1 1
2
3
4
5
SrNo 1 2 3 4 5
Candidate Vijay Chau Pranit Sha Nilesh Jha Komal Sha Ritesh Delve
Field Science CommerceCommerceScience Arts
Qualificati Gratuate HSC Graduate Graduate SSC
Work Exper 2 0.5 3 1 1
Remark
as shown below

Candidate Field Qualificati Work ExperRemark

Vijay Chau Science Gratuate 2
Pranit Sha CommerceHSC 0.5
Nilesh Jha CommerceGraduate 3
Komal Sha Science Graduate 1
Ritesh Del Arts SSC 1
IF Function: Put 1 of 2 things into a cell based on a logical test
AND Function: When all logical tests are passed, AND delivers a TRUE
If any one of the tests come out FALSE, AND delivers a FALSE
OR Function: When at least 1 logical tests is passed, OR delivers a TRUE
If any one of the tests come out TRUE, OR delivers a TRUE

Example 1: IF function
Example 2: AND function with 2 logical tests inside IF function
Example 3: OR function with 2 logical tests inside IF function
Example 4: AND function with 3 logical tests inside IF function
Example 5: AND and OR Functions to create a "None" Logical Test
Example 6: AND and OR Functions to create a complex Logical Test

IF Function when there is only 1 Logical Test

Hurdle to Extend Credit:
Credit Rating 6 out of 10
Test:
1 Condition Must Be Met To Extend Credit Credit Rating >= 6
Customer Credit Rating Logical Test/Formula Extend Credit?
Customer 1 6.0 1 extend credit
Customer 2 5.5 FALSE
Customer 3 6.0 TRUE
Customer 4 3.5 FALSE
Customer 5 6.2 TRUE

IF Function with AND Function when there are 2 Logical Tests

Hurdle to Extend Credit:
Credit Rating 6
Sales $500,000.00

2 Conditions/Criteria Must Be Met To Extend Credit

Customer Credit Rating Last Years Sales Extend Credit?
Customer 1 4.0 $1,043,462.00 no credit
Customer 2 5.5 $792,157.00 no credit
Customer 3 6.0 $831,594.00 extend credit
Customer 4 3.0 $335,410.00 no credit
Customer 5 6.2 $490,000.00 no credit

IF Function with OR Function when there are 2 Logical Tests, but only 1 must be true
Hurdle to Extend Credit:
Credit Rating 6
Sales $500,000.00
 1 Condition or the other or both ("At Least 1") Must Be Met To Extend
Credit
Customer Credit Rating Last Years Sales Extend Credit?
Customer 1 4.0 $1,043,462.00 extend credit
Customer 2 5.5 $792,157.00
Customer 3 6.0 $831,594.00
Customer 4 3.0 $335,410.00
Customer 5 6.2 $490,000.00

IF Function with AND Function when there are 3 Logical Tests

Hurdle to Extend Credit: Test:
Credit Rating 1 6 out of 10 Credit Rating 1 >= 6
Sales $500,000.00 AND Sales >= 500000
Credit Rating 2 2.5 out of 5 AND Credit Rating 2 > 2.5

3 Conditions/Criteria Must Be Met To Extend Credit

Customer Credit Rating 1 Credit Rating 2 Last Years Sales Extend Credit?
Customer 1 4.0 1.3 $1,043,462.00 no credit
Customer 2 6.0 2.7 $792,157.00 extend credit
Customer 3 7.0 2.6 $831,594.00 extend credit
Customer 4 3.0 1.4 $335,410.00 no credit
Customer 5 6.2 3.2 $490,000.00 no credit

AND and OR Functions together to create "None" Logical Test

Hurdle to Extend Credit: Test:
Credit Rating 1 6 out of 10 Credit Rating 1 >= 6
Sales $500,000.00 AND Sales >= 500000
Credit Rating 2 2.5 out of 5 AND Credit Rating 2 > 2.5

3 Conditions/Criteria Must Be Met To Extend Credit

Customer Credit Rating 1 Credit Rating 2 Last Years Sales No Test Passed
Customer 1 4.0 1.3 $1,043,462.00 no credit
Customer 2 6.0 2.7 $792,157.00 extend credit
Customer 3 7.0 2.6 $831,594.00 extend credit
Customer 4 3.0 1.4 $335,410.00 no credit
Customer 5 6.2 3.2 $490,000.00 no credit

IF Function with AND and OR Function

Hurdle to Extend Credit: Test:
Credit Rating 1 6 out of 10 Credit Rating 1 >= 6
Sales $500,000.00 AND
Credit Rating 2 2.5 out of 5 Sales >= 500000
 OR
Credit Rating 2 > 2.5

1 Conditions Must Be Met and at Least One Of The Other Two Tests Must Be Met To Extend Credit
Customer Credit Rating 1 Credit Rating 2 Last Years Sales Extend Credit?
Customer 1 4.0 1.3 $1,043,462.00 no credit
Customer 2 6.0 2.7 $792,157.00 extend credit
Customer 3 7.0 2.6 $831,594.00 extend credit
Customer 4 3.0 1.4 $335,410.00 no credit
Customer 5 6.2 3.2 $490,000.00 extend credit
Test:
Credit Rating >= 6
AND
Sales >= 500000
Test:
Credit Rating >= 6
OR EX CR
Sales >= 500000 EX CR
EX CR
NO CR
EX CR

NO CR
EX CR
EX CR
NO CR
NO CR
NO CR
EX CR
EX CR
NO CR
EX CR
 35

Name Maths Science Hindi Marathi English Geography Total Out Of Result Percentage
naina 77 48 88 94 69 80 456 600 pass 76
jayesh 62 64 84 50 91 41 392 600 pass 65.333333333
pooja 50 80 47 80 80 80 417 600 pass 69.5
rishav 37 82 86 59 68 35 367 600 pass 61.166666667
sudeepa 78 73 60 81 74 53 419 600 pass 69.833333333
ankush 39 50 72 66 63 38 328 600 pass 54.666666667
mehak 86 75 49 39 37 70 356 600 pass 59.333333333
Name Maths Science Hindi Marathi English Geography Total Out Of Result Percentage
naina
jayesh
pooja
rishav
sudeepa
ankush
mehak

mahek mahek mahek mahek mahek mahek mahek

mahek mahek mahek mahek mahek mahek mahek
mahek mahek mahek
mahek mahek
mahek mahek
mahek mahek mahek mahek mahek
mahek mahek mahek mahek
mahek mahek
mahek mahek
mahek mahek
mahek mahek mahek
mahek mahek
 Grade Rank
b 1 408 if result pass he to grade dena he
b 4 >90 A
b 3 >65 B
c 5 >50 C
b 2 elsa D
c 7 or agr pass nhi he to work hard
c 6
Grade Rank

mahek mahek mahek mahek mahek mahek

mahek mahek mahek mahek mahek mahek
mahek mahek mahek
mahek mahek mahek
mahek mahek mahek
mahek mahek mahek
mahek mahek mahek
mahek mahek mahek mahek mahek mahek mahek
mahek mahek mahek mahek mahek mahek mahek mahek
mahek mahek mahek mahek mahek mahek mahek mahek
mahek mahek mahek mahek mahek mahek mahek
mahek mahek mahek mahek mahek mahek mahek
mahek mahek mahek mahek mahek mahek mahek
Go To File > Formula Tutorial>skip vlookup
 Aspire INVOICE
Mira raod

Telephone: 0123456789 Date: January 1st 2011

Fax: 0123456789 Invoice No. 143/13-1
Mobile: 0123456789 Customer Ref: T222-1

Invoice to XYZ Ship To ABC

Mira Road East Bengaluru
Thane Maharashtra Karnataka

Product ID Description Quantity Unit Price

Sub Total
Tax Rate
Tax Rate
Handling & Shipping
Total

Please make all cheques payable to "company name"

or make payment to bank account number: 12345678 sort code: 01-01-01

Thank You For Your Order

NVOICE
uary 1st 2011

Row Total

de: 01-01-01
 Score Debut
Player
ODI TEST T-20 IPL ODI TEST T-20 IPL
Sachin Tend 18,426 15,921 10 2334 1989 1989 2006 2008
Kapil Dev 3,783 5,248 0 0 1978 1978 - -
Rahul Dravi 10,889 13,288 31 2174 1996 1996 2011 2008
Yuvraj Singh 8,329 1,900 1134 2216 2000 2003 2007 2008
Virendra Se 8,273 8,586 394 2431 1999 2001 2006 2008
MS Dhoni 9,058 4,876 1112 3207 2004 2005 2006 2008
Sourav Gang 11,363 7,212 0 1349 1992 1996 - 2008
Virat Kohli 7,755 4,310 1709 3814 2008 2011 2010 2008

Try Solving Following Conditions

• Highlight GREEN Cell Color to Player Have Scored the HIGHEST in IPL.
• Highlight BLUE Cell Color to Player Have Debuted in 1996
• Highlight YELLOW Cell Color to Player Have Scored Above 9000 in ODI
• Highlight RED Cell Color to Player Have Scored the Least in TEST
• Highlight PURPLE Cell Color to Player Have Scored Between 1000 to 2500 in T-20

Problem 02 Create an Excel Worksheet As Shown in the Figure

Sales perso May June July Aug

Albertson Ka 3947 557 3863 1117
Allenson Car 4411 1042 9355 1100
Atman Zoey 2521 3072 7602 2116
Bittiman Mic 4752 3755 4415 1089
Brennan Wil 4964 3152 11601 1122
Carlson Davi 2327 4056 3726 1135
Collman Har 3967 4096 9007 2133
Counts Eliza 4670 521 4505 1024
David Chole 3379 3428 3973 1716

Try Solving Following Conditions

• Highlight GREEN Cell Color to The HIGHEST Sales Amount of May
• Highlight RED Cell Color to The LEAST Sales Amount of June
• Apply Data-Bar of Green Color to Display of Performance Graphically in July
• Highlight YELLOW to TOP 3 Sales Amount of of Aug
 population of wildlife
month bears dolphins whales population of wildlife
jan 8 150 80
200 184
feb 54 77 54
180
mar 93 32 100 150
160 may; ₹ 137.00 jan
apr 116 11
140
76 feb
116
may 137 120
6 93 mar
100
93 may; ₹ 93.00 apr
jun 184 1001 72 80
77 76 72 may
80 jun
54 54
60
32
40
8 11 ₹ 6.00
may;
20 1
0
bears dolphins whales
year bears dolphins whales
2017 8 150 80
Chart Title
2018 54 77 54 200
2019 93 32 100
2020 116 11 76
150
2021 137 6 93
2022 184 1 72
100

month rainy day profit

jan 12 ₹ 3,574.00 50

feb 11 ₹ 4,708.00
mar 10 ₹ 5,332.00
0
apr 9 ₹ 6,693.00 2017 2018 2019 2020 2021 2022
may 8 ₹ 8,843.00
Chart Title
jun 6 ₹ 12,347.00
14 1
jul 4 ₹ 15,180.00
aug 6 ₹ 11,198.00 12 1
sep 7 ₹ 9,739.00 10
1
oct 8 ₹ 9,846.00 1
8
nov 10 ₹ 6,620.00 8
dec 11 ₹ 5,085.00 6
6
4
4
2 2
0 0
jan feb mar apr may jun jul aug sep oct nov dec

rainy day profit

t Title

dolphins
whales

2020 2021 2022

16000
14000
12000
10000
8000
6000
4000
2000
0
ug sep oct nov dec

profit