Tags: BLANK

Determine number of learners who have completed different stages of multiple online courses

{4 Comments}

Here is a sample dataset of learners who have cleared different stages of multiple courses on offer within an Organisation:

Learner Stage completed Course
Bill Stage 1 Public Speaking
Bill Stage 2 Public Speaking
Bill Stage 3 Public Speaking
Susan Stage 1 Effective Communication
Bob Stage 1 Public Speaking
Bob Stage 2 Public Speaking
Sheila Stage 1 Effective Communication
Sheila Stage 2 Effective Communication
Sheila Stage 3 Effective Communication
Frank Stage 1 Effective Communication
Frank Stage 2 Effective Communication
Henry Stage 1 Public Speaking
Henry Stage 2 Public Speaking
Bill Stage 1 Effective Communication
Bill Stage 2 Effective Communication

From this sample dataset, one may want to know how many participants have completed each stage of these multiple courses.  The expected result is shown below:

Row Labels Stage 1 Stage 2 Stage 3
Effective Communication 1 2 1
Public Speaking 2 1
Grand Total 1 3 2

In this workbook, I have shared 2 solutions - one using formulas and the other using the Power Query & PowerPivot.

Identify buy and sell break points

{0 Comments}

Assume a two column dataset with Date in the first column and Price in the second one.  The purpose is to identify times to buy and sell - buying would be just after the lowest low is confirmed and sell before or just after the highest high is in place. Confirmation is achieved through crossover of moving averages. This data is being used in back testing buy and sell criteria.

Snapshot of base data

Snapshot of expected result

The Lowest Low is the lowest price that occurs before the next Highest High.  The Highest High is the highest price that occurs before the next Lowest Low..  2.77 is the lowest low after the highest high of 3.69 and 3.23 is highest high after the lowest low of 2.77.

You may refer to my solution in this workbook.

Perform a Competitor, Feature and Customer Analysis with the PowerPivot

{2 Comments}

Assume there are four interrelated tables. One may want to create a pivot that allows one to filter data by using the slicers. Data should be filtered by the following interdependent slicers selections: Customer, Country and segment.  The logic behind the pivot when using the slicers shall be as follows:

1. Feature N is only shown if relevant to Customer X in Segment Y and Competitors do not possess Feature N
2. Competitor X is only shown if Competitor X exists and is active in Country Z and is relevant to Customer Y in Country Z

So after slicer selections are made, the idea is to display all features that one can offer and are relevant to the respective customer in the respective segment and country, regardless of whether the competitors can offer them or not.  So if one competes with competitor 1 in a specific project and offers features 1, 3, 7, offering the very same features to our shared customer does not make sense.  The customer won't see a benefit in choosing me over competitor 1.

Here's an elaborate example:

1. If one selects Customer 1/Segment A/Country 1 from the three slicers, then the Pivot Table should display as follows:

1. Row Labels - Display features in pivot if they are implemented or relevant.  The ones in Blue are implemented and the ones in green are relevant.  The pivot now shows that competitor 1 does not have features 2 and 9 which. Thus one's sales pitch will focus on offering features 2 and 9.  This section should also show data for Feature benefit calculation.

2. Column labels - Competitor 1 and 2 are displayed in the Pivot Table because they are both relevant in Country 1.

3. Competitor has/does not have feature (Value area section) - The following competitors have the same features I can offer my customer 1 in segment A:

  • Competitor 1 has features           1, 3, 7
  • Competitor 2 has features           3

Therefore they are marked with an “x” whenever they have the features.

The other competitors also have features that I offer to customer 1 in segment A, but since they are not active in Country 1, they are automatically not displayed when using the slicers.

You may refer to my solution in this workbook.

You may also view a video of my Power Query solution here:

Story telling with Excel Power BI

{6 Comments}

With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations.  Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story.  To be able to interact with/create visualizations, you will need to install Microsoft Office Professional Plus 2013 (this version will already have two of the four components of Power BI - PowerPivot and Power View).  Additionally, you will have to install the following add-ins from Microsoft (the other two components of Power BI)

1. Power Query; and
2. Power Map

I have tried to showcase the prowess of Power BI tools of Excel 2013 in these two workbooks:

1. An overview of the BRIC Economies
2. Sales data analysis

You may watch a video of my work at this link

Analysing customer walkin data by date and service taken

{5 Comments}

Assume data is in range A3:E10.  In A4:A10, various service types are mentioned.  In B3:E3, dates are mentioned from June 1, 2012 to June 4, 2012.  In range B4:E10 are numbers representing number of customers.  One may want to answer the following questions from this data:

1. For every date, total number of customer walkins and total number of services taken; and
2. For every date, new customer walkins and new services taken; and
3. For every date, repeat customer walkins and repeat services taken

While the first and third questions are fairly straight forward to solve, some deliberation would be required for the second question.  A new service type taken on June 3, 2012 would be one that has not been taken by any customer from June 1 - 2, 2012.  So if cell A8 has Service type E and cell D8 (data for June 3, 2012) has 3 (3 customer took service type E on June 3, 2012), then this service should be counted only if there is no figure in range B8:C8 i.e. no customer took this service on June 1 - June 2, 2012.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in and the PowerPivot add-in, then a few simple steps and minimal DAX formulas can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system's performance.

You may refer to my solution in the this workbook.