QUANTITATIVE AND
COMPUTATIONAL
REASONING
LAB REPORT 4
Submitted by: Hafsa Lariab
Roll Number: Num-BSMATH-2024-12
Submitted to: Sir Shahzad Arif
Task 1:
In the provided Excel sheet sales_data.xlsx (file provided), calculate the total sales for each
salesperson using the SUM formula. Then, find the average sales using the AVERAGE
formula, and use the IF statement to determine whether the sales exceeded $5000 (display
"Target Met" or "Target Not Met")
Solution:
1
Task 2:
Copy the IF formula you created in Task 1 for all salespersons to determine if they met
their sales target.
Solution:
Formula: =IF(H2:H44 > 5000, "Target Met", "Target Not Met")
2
Task 3:
Find the maximum, minimum, and standard deviation of the sales data using the MAX,MIN,
and STDEV functions.
Solution:
Task 4:
Create the following charts from the sales_data.xlsx:
1. A Bar Chart comparing the sales of each salesperson.
Sale_amt
120,000.00
100,000.00
80,000.00
60,000.00
40,000.00
20,000.00
-
Sigal
Alexander
David
Steven
Alexander
Alexander
Alexander
Steven
David
David
Steven
Steven
Karen
Karen
Karen
2. A Pie Chart showing the percentage of total sales for each salesperson.
Sale_amt
Alexander
Shelli
1%
2%
1% 1%9%
4%
0%
1%
0%
0%
3% 2%
3%
5% 0%
5% Luis
2% 2%
6% 7% David
0%
3%
2%
0% 8% Stephen
6%
0% 3%
1%
1%
0%
3%3% 8%2%
1% Alexander
Steven
3
3. A Line Chart to display the sales trend over a period (if time data is available).
Sale_amt
120,000.00
100,000.00
80,000.00
60,000.00
40,000.00
20,000.00
-
Task 5:
Add a title, axis labels, and data labels to the bar chart you created in Task 4.
Sales Comparison of Each Salesperson
120,000.00
100,000.00
Sales Amount
80,000.00
60,000.00
40,000.00
20,000.00
-
Steven
Steven
Steven
Steven
Sigal
Alexander
David
Alexander
Karen
Alexander
Karen
Karen
Alexander
David
David
Sales Persons
Figure 1: Bar graph comparising Sales Amount vs Sales Persons
4
Additional Tasks:
1. Create a scatter plot from a new dataset that includes hours worked and sales
figures
Salesperson Hours Worked Sales Figures ($)
John 40 6,500
Sarah 35 4,200
Mike 42 5,800
Emma 30 3,500
Liam 45 7,200
Olivia 38 5,300
Noah 36 4,900
Ava 32 6,000
Ethan 44 8,100
Sophia 33 4,400
Sales Figures (Scatter Chart)
9,000
8,000
7,000
6,000
5,000
4,000
3,000
2,000
1,000
0
0 10 20 30 40 50
2. Explore advanced functions like VLOOKUP and COUNTIF in your dataset
VLOOKUP
=VLOOKUP("Liam", A2:C11, 3, FALSE) Solution: 7200
COUNTIF
=COUNTIF(B2:B11, ">40") Solution: 3
5
6
