You should know how to use the following 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.

## Download the Practice Files

## Problem Overview

This is a dataset about a small electronics retail company. There are two sales employees, working on separate shifts. The payment is per shift. 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 is provided in the “Reference Data” sheet. The “Solution” sheet contains the answers. 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 the 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 the Income for Employes based on Shift**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.

Here’s an overview of the completed sheet.

Get FREE Advanced Excel Exercises with Solutions!
Hi, Can you help me with few things..

1. Attendance Report for 365 days which should be able to get data from Teams and update status of employees when they update as Present in Microsoft Teams.

2. Same data should be added in preparing Dashboard with Masterfile, Downtime, Employee Production count as per day wise and Pivot Table at end. All pivot table data should be shared every month with Team members on regular basis.

All above tasks should be automated…. Thanks Support. Appreciate your time…

Dear

Datla Srinivas,We are working on your requirements. We hope we will come back to you with your expected solutions.

Regards

Shamima SultanaProject Manager | ExcelDemyDear

Datla Srinivas,We have sent you an email. Please find the attached documents in it.

Best wishes

Md. Abu Sina Ibne AlbaruniTeam ExcelDemyHi

SUMBO,We have sent you an email. Please see the attached document in it.

Regards

Md. Abu Sina Ibne AlbaruniTeam ExcelDemy

Can i get a videos of solving this problem cause am lost solving them

Hello Exceldemy team,

pls how can i get an off line course for beginners, planning on getting into data analysis. Will greatly appreciate your prompt response.

Thanks

Bolajoko Olufemi

Dear

Bolajoko Olufemi,You will get all resources related to Data Analysis here

All About Data Analysis. We are planning to launch courses as soon as possible.Regards

ExcelDemyIt looks Profit Calculation is wrong in practice file…

profit percentage = (selling price – cost price) / cost price. Our calculation is correct. Additionally, commission is not usually included in the profit calculation.

Could you please clarify the difference between profit margin and profit percentage?

Hiii

I am not able to understand

Jacob and ben

How to calculate their

Day and night shift for each.

???

Hi Farhan,

Hope you are doing well.

Here

JacobandBenare sales representative working in both night and day shifts.In order to calculate their day and night shift from the list for each we used a simple formula using the

COUNTIFSfunction.First, the

COUNTIFSfunction counts the number of cells in thecell range($C$5:$C$92) matching the value incell(K23) which indicates sales representativeBen.Next, it also match the value in

cell(L22) within thecell range($D$5:$D$92).As a result, we will get the result counting total number of day shifts attended by

Ben.Similarly, we can calculate the total number of shifts attended by

Jacob.Hope you found the answers for your submitted queries.

Regards

Wasim AkramExcelDemyDear Fardeen,

The profit margin is the percentage you get from the selling price. The formula for profit margin is:

(Selling Price-Cost Price)/Selling Priceor,Net Profit/Selling PriceThe profit margin is calculated in the worksheet with the formula:

=(F5-E5)/F5On the other hand, the profit percentage is calculated based on the Cost Price. The formula for profit percentage is:

(Selling Price-Cost Price)/Cost Priceor,Net Profit/Cost PriceThe profit percentage is calculated in the worksheet with the formula:

=(F5-E5)/E5Regards

ExceldemyWhere is solution sheet?

Dear

Nikhil Pandey,You will get the solution sheet in the Download Practice Files section. Download the workbook there in Solution sheet you will get all the solutions. You you can get the workbook from this link

Solution SheetRegards

ExcelDemyI wanted to know how had you find out weekday? Please write querry below.

Dear

Nikhil Pandey,You will get all the solutions in the workbook’s Solution sheet. Here I’m giving you the query answer for your understanding.

The Formula is :

=WEEKDAY(B5,11)Regards

ExcelDemyHardest formula commission i am not able to calculate the comission for both is there any other easy way ????????

Hello FARHAN,

Hope you are doing well. I can see the formula in this article to calculate commission is a bit tricky. However, you can use the below procedure to calculate commission. I believe this formula is easier than the previous one.

`=IF(K5< 12%,0,IF(K5 <= 20%, I5 *0.005,I5 *0.015))`

Here is the final output after applying the formula to calculate the commission.

Hello ExcelDemy Team ,

Your responses always appreciable

in Formula WEEKDAY(B5,11) What is 11 here please clarify

Hello

Akram Siddique,Here, in the formula : =WEEKDAY(B5,11)

11 means – Numbers 1 (Monday) through 7 (Sunday). Here the weeks starts from Monday. So it will count the days of week from Monday and the integer value will be 1, Tuesday (2) and so on.

Regards

ExcelDemyWhere can I find more practice sheets like this?

Hello

Drishti,Here are some article you will find more exercises.

Excel Practice Exercises PDF with Answers

Sample Excel File with Employee Data for Practice

Advanced Excel Exercises with Solutions PDF

Regards

ExcelDemy