Test Your Microsoft Excel Skill Level – Case Study 6

A construction company has 15 project managers and 10 project sites. They work only 15 days in a month. Every month, a project manager will be assigned to a site to inspect the work status. Project manager will be asked to visit the site on any day within 15 working day time frame.

Their excel file has three sheets,

Sheet 1 – (named as Team) – Contains the list of 15 project manager name.

Sheet 2 – (named as Work Allocation) – This will have project manager name under the project site column

Sheet 3 – (named as Unused Resource) – We need to build a formula which will display the project manager names who have not been assigned to any project site.

Download the excel file

You can post your solution in the comments section

Test Your Microsoft Excel Skill Level – Case Study 5

A company rolled out an employee engagement initiative program. They collected the feedback from the employees. Feedback scale ranges from a to z. Have a look at the below screenshot to understand how the data is captured in the feedback excel report.

We need to find the lowest, highest and average rating provided by each candidate. This must be solved without creating any extra column or table.

Download the excel file by clicking here

You can post your solution in the comments section

Test Your Microsoft Excel Skill Level – Case Study 4

We have a 3-stage invoice clearing process. Have a look at the below screenshot to understand how the data is captured in the invoice status excel report.

Based on words mentioned under each stage, the invoice status is categorized into two main categories. They are CLEARED and PENDING UNDER STAGE 1 / STAGE 2 / STAGE 3.

Criteria to decide a invoice status category is listed below:

PENDING -> If any of the stage contains the word PENDING, the status should be mentioned as PENDING UNDER STAGE <stage number should be mentioned like STAGE 1 / STAGE 2 / STAGE 3)

CLEARED -> If all the stage has the word CLEARED, the status should be mentioned as CLEARED.

We need to use a simplest formula to derive to this solution. Result should appear like the screenshot given below (it is just a sample screenshot; results might differ in the excel given for downloading)

Download the excel file by clicking here

You can post your solution in the comments section

Test Your Microsoft Excel Skill Level – Case Study 3

We have a 3-stage shipment process. Have a look at the below screenshot to understand how the data is captured in the shipment tracker excel report.

Based on each stage status, the consignments are categorized in two main categories. They are Category-A and Category-B.

Criteria to decide a consignment’s category is listed below:

Category – A -> Stage 1 should contain the word “pending” and for stage 2 and stage 3 if both of them contains the word “pending” or either one of them contains the word “pending”.

Category – B -> All except the category – A will go under this bucket.

We need to use sumproduct formula along with any other formula which will give us how many consignments goes under Category-A and how many under Category-B.

Download the excel file by clicking here

You can post your solution in the comments section

Test Your Microsoft Excel Skill Level – Case Study 2

A company has one of their invoice reports, where invoices are maintained sheet wise in a single workbook. Structure of the invoice is same across the sheets. Refer the screenshot given below:

Steve who is working as an executive in finance department, wants to create a summary of all the invoices in a single table as line items in a separate sheet. Refer the screenshot below for the required output format:

Once the master sheet is ready, Steve would further take this summary table as a source for a pivot table for his further data analysis.

We need to help Steve by building a formula which will help him to pull the required details from all the invoice sheets irrespective of numbers. Formula which we are building to extract details from the first invoice sheet, we should be able to copy and paste the same formula for the remaining invoices, same should work even when they add new invoice sheets in future.

Download the excel file by clicking here

You can post your solution in the comments section

Test Your Microsoft Excel Skill Level – Case Study 1

A company has three HR professionals (John, Jason and James) who takes care of training activities. John is responsible for technical training; Jason is responsible for functional training and James is responsible for behavioral training.

Training department manages training tracker (excel report) in which they have created individual sheet for each training activity like technical, functional and behavioral.

As an when they complete a training, HR person updates his respective sheet with participant emp code, date of training and name of training.

In this training tracker there is a master sheet, in which they need to see employee-wise completion of each training. Completion status should be displayed in such a way Y – for completion and N – for non-completion.

Using a VLOOKUP formula, you need to resolve this. Formula you derive for technical training completion status, we should be able to simply copy and paste the same formula to check the status of other two training completion.

Final output should look something like the below image. Below image is only an example screenshot hence your solutions will not match with it.

You can post your solution in the comments section

You can download the excel file by clicking here

POWER OF COUNTIF FORMULA WITH WILDCARD CHARACTERS

Hi Folks,

In this posting we shall discuss how to use the wildcard characters “?“and “*“ in the COUNTIF formula to get some great results.

To understand this better, I have presented an example below

First formula will help us to get the count of the text entries alone

Second formula will help us to get the count of numerical values alone

Final formula will help us to get the count of those text entries whose length is 3 characters

If you use the COUNTIF formula with wildcard characters taking a formula column as a source range, then this behaves in completely different manner.

1st formula counts everything including the null entries returned by the formula.

2nd formula will count every entry excluding the null entries returned by the formula.

Hope you got some new ideas how to use the wildcard characters with COUNTIF formula.

Happy Learning!!

FINDING TOP 3 CONTRIBUTORS IN YOUR DATA USING INDEX AND LARGE FORMULA

Hi Folks,

In this posting we are going to understand how to use INDEX formula along with LARGE formula to extract the top performer details from your data table.

Let us take an example to understand this concept better

In the below table I got a list of salesperson names and their respective sales. In this list of data, I want to find top 3 contributing salesperson and their sales value.

Using LARGE formula to find the 1st, 2nd and 3rd largest values in the sales value column.

Using MATCH formula to find the position of these values in sales value column

Using INDEX formula to extract the salesperson names using the positions given by MATCH formula

Here comes the beauty when the formulas are put together. Amazing isn’t it??

Happy Learning…

 

Calculating Net Present Value Using NPV Formula

Hi Folks,

In this posting I am going to get into something which is financial. We are going to understand formula called NPV.

NPV stands for Net Present Value.

When should you use this formula?

When you want to compare two investments made in two different projects, this formula can tell us which is yielding better return.

You need to have a cash flow data from the start of the year when you made the investment, cash flow of each year should be also available. I have given you a screenshot to understand this better

First row in the screenshot shows the initial investment made for the projects. Cash flow for the remaining years are tracked and we are keeping the discount rate same for both the projects.

By keeping this data, we are using the NPV formula to find which project is yielding us better returns.

Syntax of the formula would

=NPV(INTEREST RATE, SELECT THE CASH FLOW VALUES(EXCEPT THE INITIAL YEAR)) + INITIAL YEAR INVESTMENT VALUE

If you closely the above screenshot, I have used the formula in the way which is explained above. We have the NPVs for both the projects.

Results are showing very clearly that, Project 2 is yielding better results than Project 1.

You can use this same formula to find out the better yielding investments who are promising same percentage of returns.

Hope this posting helped you in understanding how to use NPV at a basic level.

Happy Learning !!

Building Excel Custom Format Using Format Cells

Hi Friends,

In this posting we are going to discuss about how to use “Format Cells”, when you want to build your own custom formats for your values. Using FORMAT CELLS feature, you can change the format of the selected cell to any of the format from the available options like NUMBER, PERCENTAGE, CURRENCY etc.

If you want to build your format for your values, you should know the nuances of format cells.

Any custom format you want to build, you should define four inputs for it. You need to specify how positive value, negative value, zeros and text should be formatted.

Syntax would be

POSITIVE VALUES ; NEGATIVE VALUES ; ZEROS ; TEXT VALUES

Let us see few examples to understand this better;

In the above screenshot we have positive values, negative values, zeros and text values. Our requirement is, positive values should appear along with a word “BUY”, negative values should appear with a word “SELL”, zeros should appear along with a word “NO CHANGE” and finally for text values it should be appear as “NO DATA”

Selecting the RESULT column values in the above screenshot, go to “Format Cells” by using shortcut key CTRL+1 or you can simply right click on the selected cells and choose the option “Format Cells”

In the FORMAT CELLS option, go to the category “CUSTOM” and on the right-hand side of the dialog box under the word TYPE, clear the content in that box and start typing your custom format.

“BUY” 0.0 ; “SELL” 0.0 ; “NO CHANGE” ; “NO DATA”

Finally the values will start appearing in your required custom format, please refer the below screenshot;

If you closely observe this process, this changes only the appearance of the values. It will never disturb the actual value; these values can be used for any other calculations as well.

Give a try from your side to design your own formats for your values.

Happy Learning !!