Scribd
Upload
13 views2 pages

Excel Basic Math Functions Guide

The document outlines a practical exercise for learning basic Excel functions including SUM, AVERAGE, MIN, and MAX using a dataset of monthly sales from various store locations. It provides step-by-step instructions for calculating total sales, average monthly sales, and identifying the highest and lowest sales months, along with formatting and data manipulation tasks. The exercise aims to enhance data analysis skills in a supermarket chain context.

Uploaded by

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

Excel Basic Math Functions Guide

The document outlines a practical exercise for learning basic Excel functions including SUM, AVERAGE, MIN, and MAX using a dataset of monthly sales from various store locations. It provides step-by-step instructions for calculating total sales, average monthly sales, and identifying the highest and lowest sales months, along with formatting and data manipulation tasks. The exercise aims to enhance data analysis skills in a supermarket chain context.

Uploaded by

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

Practical Exercise: Basic Math Functions in Excel

Objective:

• Learn to use fundamental Excel functions: SUM, AVERAGE, MIN, and MAX.
• Work with a large dataset for practical application.

Scenario:

You are a data analyst for a supermarket chain and need to analyze the monthly sales
performance of different store locations.

Dataset: Monthly Sales Report

Instructions:

1. Open Microsoft Excel and create a new worksheet.


2. Enter the following dataset manually or copy-paste it into your worksheet.

Store Store January February March April May June


Location
ID Name ($) ($) ($) ($) ($) ($)
101 Alpha Mart New York 45,000 47,500 50,200 52,000 54,500 55,000
102 Beta Store Chicago 38,000 41,200 43,500 45,600 48,900 50,300
Gamma Los
103 50,500 52,000 53,800 55,400 58,200 60,000
Mart Angeles
Delta
104 Houston 33,000 34,500 37,800 39,900 42,400 44,500
Market
Omega
105 Miami 42,500 44,000 46,500 48,900 50,000 51,500
Store
106 Sigma Mart Dallas 39,500 41,200 42,600 45,000 47,300 49,700
107 Zeta Mart Atlanta 29,000 31,500 34,000 36,200 39,500 41,800
Epsilon
108 Boston 51,000 52,800 55,200 58,400 60,500 62,300
Mart
109 Theta Store Phoenix 40,200 42,500 45,300 47,600 50,100 52,500
Lambda
110 Seattle 48,500 50,300 52,700 54,900 57,200 59,000
Mart

Task 1: Apply Basic Math Functions


1. Calculate the Total Sales for Each Store (SUM):
o In a new column (Total Sales), enter the formula:

=SUM(D2:I2)

o Copy the formula down for all stores.


2. Find the Average Monthly Sales (AVERAGE):
o In a new column (Average Sales Per Month), enter the formula:

=AVERAGE(D2:I2)

o Copy the formula down for all stores.


3. Find the Highest and Lowest Monthly Sales (MAX & MIN):
o In a new column (Highest Sales Month), enter the formula:

=MAX(D2:I2)

o In a new column (Lowest Sales Month), enter the formula:

=MIN(D2:I2)

o Copy both formulas down for all stores.

Task 2: Apply Formatting

1. Bold the headers and apply a background color for readability.


2. Format the sales columns as Currency ($).
3. Adjust column widths to fit the data properly.

Task 3: Sorting & Filtering the Data

1. Sort by Total Sales in descending order (Highest to Lowest).


2. Filter to display only stores with an Average Sales Per Month above $50,000.
3. Filter to find stores where the Highest Sales Month was above $60,000.

You might also like