Scribd
Upload
36 views1 page

Practical Excel Exercise Answer

The document contains a sales data sheet listing customer orders, quantities, prices, and total sales categorized into Low, Medium, and High. It provides formulas for calculating total sales and categorizing them based on their values, along with a summary of total sales by category. Additionally, it suggests creating a pie chart to visualize the sales distribution across categories.

Uploaded by

jerryguo0321
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
36 views1 page

Practical Excel Exercise Answer

The document contains a sales data sheet listing customer orders, quantities, prices, and total sales categorized into Low, Medium, and High. It provides formulas for calculating total sales and categorizing them based on their values, along with a summary of total sales by category. Additionally, it suggests creating a pie chart to visualize the sales distribution across categories.

Uploaded by

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

Model Answer

Sales Data Sheet


Order Customer Quantity Price per Total Sales
Product
ID Name Sold Unit Sales Category
1 Alice Smith Widget A 5 20 100 Medium
2 Bob Johnson Widget B 10 15 150 Medium
3 Carol White Widget C 2 60 120 Medium
4 David Brown Widget D 1 80 80 Low
5 Emma Wilson Widget E 20 25 500 High
6 Frank Taylor Widget F 4 30 120 Medium
7 Grace Lee Widget G 15 10 150 Medium
8 Henry King Widget H 25 2 50 Low
9 Isla Scott Widget I 12 45 540 High
10 Jack Harris Widget J 8 12 96 Low

Total Sales Formula (F2):


=D2*E2

Sales Category Formula (G2):


=IF(F2<100, "Low", IF(F2<=500, "Medium", "High"))

Summary Sheet
Sales
Total Sales
Category
Low 226
Medium 420
High 540

SUMIF Formulas:
 Low: =SUMIF('Sales Data'!G:G, "Low", 'Sales Data'!F:F)
 Medium: =SUMIF('Sales Data'!G:G, "Medium", 'Sales Data'!F:F)
 High: =SUMIF('Sales Data'!G:G, "High", 'Sales Data'!F:F)

Pie Chart
 Create a pie chart based on the Total Sales in the Summary sheet to visualize the
proportion of each Sales Category.

Documentation Comment
In the Sales Category column, I used a nested IF function to categorize the Total Sales. The
formula checks if the Total Sales value is less than 100, between 100 and 500, or greater
than 500, and assigns the appropriate category ("Low", "Medium", "High").

You might also like