In this article, you can download an Excel file with data for practice for free. In this file, there are five questions for you to solve. The first four problems are easy to solve, however, the last problem needs a little more advanced Excel knowledge. Therefore, if you have advanced knowledge in Excel, you are good to deal with these problems. You need to know these functions: IFERROR, IFS, AND, COUNTIFS, SUM, COUNTIF, SUMIF, WEEKDAY, and SUMPRODUCT. Additionally, you should know about the Conditional Formatting and change the font color to get the solution to the problem. If you have at least Excel 2010, you can use all of the applied features.
Download Practice Files
You can download the Excel file from the following link.
This is a dataset about a small electronics retail company. There are two sales employees, working on separate shifts. The payment is shiftwise. The night shift pays more. The cost price and selling price are the accumulation of all items sold on each shift per day. We have 5 tasks to solve. Additional data are provided in the “Reference Data” sheet. These are solved in the “Solution” sheet. Here is a snapshot of the “Problem” sheet, which is only showing the first 19 rows of the 88 rows from the dataset.
- Task 01 – Find the Profit Percentage for Each Sale.
- Solution: You can use the formula “(selling price – cost price)/cost price” to find the profit. Then, you can divide the profit by the cost price to get the profit percentage.
- Task 02 – Calculate commission for sales.
- Solution: A certain profit percentage will reward the salesman with a commission. The exact amount is provided in the “Reference Data” sheet. You can integrate that data with a conditional formula. We have created ours by combining the AND, IFERROR, and IFS functions. Moreover, the following image shows the sheets of our Excel file.
- Task 03 – Count the number of shifts for each employee.
- Solution: You can use the COUNTIFS function to calculate this. Alternatively, you can use multiple COUNTIF functions to do so.
- Task 04 – Determine Income for Both.
- Solution: The payment is higher for the night shift. So, the employee who works more on the night shift will earn more.
- Task 05 – On average, which day of the week is the most profitable for the company?
- Solution: This can be a tricky question for some. You can either solve this by creating helper columns or using the SUMPRODUCT function. We have shown both ways in the Excel file. Here, Monday is 1, Tuesday is 2, and so on. Additionally, you can use the conditional formatting to highlight the maximum value.
Lastly, after solving the problems, it will look like this.
Thank you for reading this article. We hope that with this Excel data for practice, a free download file, you can test your Excel skills. You can find more articles similar to this on the ExcelDemy website. If you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!