5. Sheets: Use the MIN, MAX, and AVERAGE Functions to Analyze Sales Data
In the previous videos, you used functions to make calculations about the costs, revenue, and profits of your fundraiser. You also might want to calculate the average price of a sale a particular team member makes or identify a customer’s maximum number of shirts ordered to see if they’d be interested in ordering more in the future.
With this data, you can find details such as which salesperson recorded the highest sales, who sold the greatest variety of shirts, and other specifics about each salesperson.
In this video, you will use the minimum, maximum, and average functions to analyze your data as it relates to individual sales people. To start, open the Sales by Team Member sheet in your project. Then, find the average sale price for the first team member. An "average" is a measure used to find the middle, or central value, of a set of numbers. To find the average, you would need to add together the set of values then divide by the number of values. To make this calculation faster, use the AVERAGE function. Type an equals sign, then begin typing “average.”
Select the AVERAGE function from the menu.
Then, select the range of numbers you’d like to include in the average -- in this case, the revenue for each order the team member placed...
and complete the function. This number represents the average cost of the t-shirts in each order. Just because someone sold the greatest number of shirts doesn’t mean their average revenue is higher -- or lower -- than the other team members. This information could help the fundraising committee decide what to prioritize selling going forward. For example, maybe the least expensive shirts are more likely to sell, which brings in more profits, while the more expensive shirts are not as popular.
Now, use the AVERAGE function to find the average sales prices for the rest of the team members, and the online sales. Then you can compare how much each team member sold on average and whether it was more or less than the amount made with online sales.
Highlight the team member with the highest average order.
Then, use the MIN function to find the minimum cost of an order placed by each team member.
This will show who made the lowest individual sale. For example, someone’s lowest sale may be one shirt at twenty dollars while another team member’s lowest sale may be one shirt at thirty-five dollars. Type an equals sign, then the letters “M-I-N” to find the minimum function in the menu.
Select the sales prices again...
and complete the function. Repeat those steps to calculate the minimum for the rest of the team members and the online orders.
Highlight the team member with the highest minimum order.
Next, use the MAX function to find the maximum cost of an order placed by each team member.
This will show who made the largest individual sale, such as one order of five sweatshirts versus one order of five tank tops. Type an equals sign, then the letters “M-A-X” to find the maximum function in the menu, select the orders, and close the function...
and complete the function for the rest of the column.
Highlight the team member with the highest maximum order.
Then, calculate the minimum, maximum, and average for each column to show a broader picture of the sales data overall. Use the MIN function to find the minimum number of shirts purchased in each order.
Then click the handle of the cell and drag over the function to the rest of the rows. Repeat those steps using the MAX function...
and the AVERAGE function.
Great work! Knowing who the leading salesperson is or the average amount spent on online orders helps you draw important conclusions. Then, you and your team can use the calculations to make better, data-driven decisions.
Now, it’s your turn: Use the AVERAGE, MIN, and MAX functions to determine the average, minimum, and maximum sale amount for each team member.
Highlight the largest average, minimum, and maximum orders.
And use the same functions to determine the average, minimum, and maximum values in the rest of the data sets.
- Use the AVERAGE, MIN, and MAX functions to determine the average, minimum, and maximum sale amount for each team member.
- Highlight the largest average, minimum, and maximum orders.
- Use the same functions to determine the average, minimum, and maximum values in the rest of the data sets.