[go: up one dir, main page]
Scribd
Upload
44 views7 pages

Tce Excel Test

The document outlines various Excel tasks including formulas for eligibility checks, target fetching, and performance ratings. It also includes instructions for creating Pivot Tables, applying conditional formatting, and highlighting duplicates. Additionally, it provides criteria for overtime pay based on hours worked and day of the week.

Uploaded by

SudeepSMenasinakai
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
44 views7 pages

Tce Excel Test

The document outlines various Excel tasks including formulas for eligibility checks, target fetching, and performance ratings. It also includes instructions for creating Pivot Tables, applying conditional formatting, and highlighting duplicates. Additionally, it provides criteria for overtime pay based on hours worked and day of the week.

Uploaded by

SudeepSMenasinakai
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 7

Write an Excel formula using IF and OR

to check if a salesperson has either met


the target or belongs to the "North"
region. If either condition is true, return
"Eligible for Bonus", otherwise return
"Not Eligible"

Salesperson Region

John North
Mike South
Emma East
Lily North
David West
Sophia South

Write an XLOOKUP formula to fetch the


Target Amount from Table 2 based on the
Salesperson's name from Table 1.
Table 1
Salesperson Sales
Amount
A 50,000.00
B 75,000.00
C 60,000.00
D 82,000.00
E 45,000.00

Salesperson
Table 2
Salesperson Target
amount
A 52,000.00
D 95,000.00
C 48,000.00
B 100,000.00
E 41,000.00

Create a Pivot Table to summarize Total


Sales by Region.
Add a filter to allow selection of a specific
Salesperson.
Show only the Top 3 regions based on
total sales.

Date Salesperson
1-Jan-25 John
2-Jan-25 Mike
3-Jan-25 Emma
4-Jan-25 Lily
5-Jan-25 David
6-Jan-25 Sophia
7-Jan-25 John
8-Jan-25 Emma
9-Jan-25 Mike
10-Jan-25 David

Apply conditional formatting to


highlight late submissions in red.
If the submission date is on or before
the due date, it should remain
unchanged.

Submission
Due Date
Date
2/20/2025 2/18/2025
2/17/2025 2/20/2025
2/22/2025 2/21/2025
2/15/2025 2/15/2025
2/25/2025 2/23/2025

An employee’s performance is rated


based on their score in Column A. The
rating scale is:

90 and above → "Excellent"


70 to 89 → "Good"
50 to 69 → "Average"
Below 50 → "Needs Improvement"

Candidate
Score
response
95
82
67
45
73
90
50
An employee gets overtime pay only if:

They worked more than 8 hours in a day


AND it was a weekday
Hours
Day
Worked
9 Monday
7 Wednesday
10 Friday
8 Sunday
6 Tuesday

Apply Conditional Formatting to highlight


duplicate Employee IDs.
Employee
ID
EMP001
EMP002
EMP003
EMP001
EMP004
EMP002
EMP005
Sales Target Candidate
Amount Amount response
55,000.00 60,000.00
45,000.00 50,000.00
70,000.00 65,000.00
62,000.00 60,000.00
48,000.00 50,000.00
75,000.00 72,000.00

ble 1
Candidate
response

ble 2

Region Sales Amount


North 55,000.00
South 45,000.00
East 70,000.00
North 62,000.00
West 48,000.00
South 75,000.00
North 53,000.00
East 68,000.00
South 47,000.00
West 49,000.00
Candidate
response
386.47343

2000
120 180
65% 80%
78 144 222 9.009009009

You might also like