Tags: IF

Show sales only for corresponding months in prior years

{0 Comments}

Refer to this simple Sales dataset

untitled

The objective is to create a simple matrix with months in the row labels, years in the column labels and sales figures in the value area section.  The twist in the question is that for years prior to the current year (2018 in this dataset), sales should only appear till the month for which there is data for the current year.  For e.g., for 2018, data is only till Month 4 and therefore for prior years as well, data should only appear till Month 4.  As and when Sales data gets added below row 17, data for prior years should also go up to that month.

The expected result is

untitled1

You may download my PBI file from here. The same solution can be obtained in Excel as well (using Power Query and PowerPivot).

Filtering on 2 date fields within one Table

{0 Comments}

This table contains a list of all the inspections created and completed within different time periods.

untitled

The objective is to create two Tables from this single table - one showing the Accounts created within the chosen time period and another showing the those that were closed within the same time period.  Here are screenshots of the expected results.

untitled1 untitled2 untitled3 untitled4

You may download my PowerBI desktop solution workbook from here.  The same solution can be obtained in Excel as well (using Power Query and PowerPivot).

Determine the most recent status after satisfying certain conditions

{0 Comments}

Assume a three column dataset with Patient ID, Smoking Status and Review Date

PatientID SmokingStatus ReviewDate
P1 10-03-2018
P1 9 09-03-2018
P1 1 08-03-2018
P1 4 07-03-2018
P2 9 10-03-2018
P2 9 09-03-2018
P2 9 08-03-2018
P2 9 07-03-2018
P3 2 10-03-2018
P3 09-03-2018
P3 9 08-03-2018
P4 9 10-03-2018
P4 1 09-03-2018
P4 4 08-03-2018

The objective is the create another 3 column dataset with the following conditions:

  1. If the patient's latest smoking status is other than Blank or 9, then consider that as the smoking status of the patient; and
  2. If the patient's latest smoking status is blank or 9, then consider the previous smoking status that is not blank or 9; and
  3. If the patient's smoking status is blank or 9 on all dates, then consider the smoking status as 9

The expected result is:

PatientID Last date when the smoking status was other than 9 or Blank Smoking status on that date
P1 08-Mar-18 1
P2 10-Mar-18 9
P3 10-Mar-18 2
P4 09-Mar-18 1

I have solved this question using 3 methods - PowerPivot, Advanced Filters and formulas.  You may download my solution workbook from here.

Combine unique entries from a range of cells after satisfying a condition

{0 Comments}

Here is a simple three column dataset showing Item ID, Catalogue Name and Catalogue Year

Item ID Catalogue Name Catalogue Year
A Spring 2012
A Spring 2016
A Spring 2017
B Fall 2014
B Fall 2014
B Fall 2016

The second and third last rows are clear duplicate rows. The task is to create a fourth column (titled as All Year) in this dataset with concatenated entries from the Catalogue Year column for each Item ID. So the expected result is:

Item ID Catalogue Name Catalogue Year All Year
A Spring 2012 2012, 2016, 2017
A Spring 2016 2012, 2016, 2017
A Spring 2017 2012, 2016, 2017
B Fall 2014 2014, 2016
B Fall 2014 2014, 2016
B Fall 2016 2014, 2016

Notice that for Item ID B, the result in every cell of the fourth column is 2014, 2016 and not 2014,2014,2016.  So while joining (concatenating in technical MS Excel terminology) entries from the "Catalogues Year" column in the "All Year" column, only unique entries for that Item ID have to be considered.

Excel 2016 has a TEXTJOIN() function which gets use close to the final result but does not take into account the unique entries only (it takes all entries into account all entries).  On using the TEXTJOIN() array formula in a spare column, here is the result i got:

Item ID Catalogue Name Catalogue Year All Year
A Spring 2012 2012, 2016, 2017
A Spring 2016 2012, 2016, 2017
A Spring 2017 2012, 2016, 2017
B Fall 2014 2014, 2014, 2016
B Fall 2014 2014, 2014, 2016
B Fall 2016 2014, 2014, 2016

Notice that for Item ID B, the result is 2014, 2014, 2017 and not 2014, 2017.  So the TEXTJOIN() function does not work.

I have solved this problem using Power Query (Get and Transform in Excel 2016).  You may download my solution workbook from here.

Prepare an invigilation schedule for each teacher by different time periods

{3 Comments}

Imagine a multi column exam invigilation schedule with the following information

  1. S. No.
  2. Name of staff
  3. Designation of staff member
  4. Two columns for each day on which there is an exam - one for Morning and another for Afternoon
  5. A * under each column if that particular staff member has to be an invigilator during that time period
S. No. Name of staff Designation 09/11/2017-Morning 09/11/2017-Afternoon 10/11/2017-Morning 10/11/2017-Afternoon
1 Tom Lecturer * * *
2 Sam Lecturer * *

This dataset stretches into many more columns.  So if the exams last for 15 days, there will be 30 columns.  The objective is to condense the column expanding dataset into a 5 column one - S. No., Name of Staff, Designation, Morning and Evening.  Under the Morning and Evening columns, the different dates have to be separated by commas in that one single cell.  The expected result is:

Name of staff S. No. Designation Morning Afternoon
Tom 1 Lecturer 09/11/2017, 10/11/2017, 11/11/2017, 13/11/2017, 14/11/2017, 16/11/2017, 17/11/2017 10/11/2017, 13/11/2017, 14/11/2017, 15/11/2017, 16/11/2017, 17/11/2017
Sam 2 Lecturer 09/11/2017, 13/11/2017, 15/11/2017, 16/11/2017 09/11/2017

I can think of 3 ways to solve this problem. Here's a brief about each of those methods:

  1. Using Power Query and Excel's functions - Using Power Query, one can first unpivot the data and then use the TEXTJOIN() function.  This would be an array formula.
  2. Using Power Query only - Using Power Query, one can first unpivot the data and then modify the code to concatenate all dates in a single cell for that invigilator.  One can then Pivot the data in Power Query itself
  3. Using Power Query and PowerPivot - Using Power Query, one can first unpivot the data and then use the CONCATENATEX() function of the PowerPivot.

As you can observe, the common thread in all solutions is that one has to first unpivot the dataset.

You may refer to all my 3 solutions in this workbook.

In a Pivot Table, compute highest revenue earned on any day from each customer and the date thereof

{0 Comments}

Here's a simple dataset showing the Date of sale, Customer Name and Sales amount.

Date Customer Name Sales amount
12-03-2017 A 1
12-03-2017 A 2
12-03-2017 A 3
12-03-2017 B 4
12-03-2017 B 5
12-03-2017 B 6
12-03-2017 B 7
12-03-2017 B 8
13-03-2017 A 1
13-03-2017 A 1

The objective is to determine Customer wise:

  1. Highest revenue; and
  2. Date on which that highest revenue was earned

The expected result is

Row Labels Highest revenue Date of highest revenue
A 6 12-Mar-2017
B 30 12-Mar-2017
Grand Total 36

So, for A, revenue earned on March 12, 2017 is 6 and on March 13, 2017 is 2.  Since the higher of the two is 6, that is the result which should appear in the Pivot Table.

I have solved this problem with the PowerPivot.  You may download my solution workbook from here.

Distribute projected revenue annually

{2 Comments}

Here is a dataset showing Project wise forecast of open opportunities.

  1. Topic is the Project Name
  2. Est. Close Date is the date by when the opportunity would be closed i.e. the project would be won from that Client
  3. Duration is the time (in months) for which the project would run
  4. Amount is the total amount that would be billed for that project

Clients are invoiced annually only. So in the example below:

  1. Project ABC is for US$1 million with a duration of 24 months and is expected to be closed in Oct. 2017.  We need to model the data to show the billing every 12 months.  So for ABC US$500K would be billed in Oct-2017 and another US$500K in Oct-2018.
  2. Project GEF is for US$2 million with a duration of 18 months and is expected to be closed in Feb. 2018. We need to model the data to show US$1.3 million in Feb-2018 and another US$666K in Feb-2019.  The monthly billing is US$2 million divided by 18 and then multiplied by 12 - this amounts to US$1.3 million.
Topic Est. Close Date Duration (Months) Amount
ABC 01-10-2017 24 1,000,000
GEF 01-02-2018 18 2,000,000
XYZ 01-03-2018 30 1,000,000

The expected result should look like this:

Row Labels Oct-17 Feb-18 Mar-18 Oct-18 Feb-19 Mar-19 Mar-20 Total
ABC 500,000 500,000 1,000,000
GEF 1,333,333 666,667 2,000,000
XYZ 400,000 400,000 200,000 1,000,000
Grand Total 500,000 1,333,333 400,000 500,000 666,667 400,000 200,000 4,000,000

I have solved this problem using Power Query and PowerPivot. You may download my solution workbook from here.

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.

Return best possible fit, to manually entered dimensions, with the intent to minimise wastage

{5 Comments}

Refer to a simple 5 column representative inventory dataset of a Glass manufacturer:

Model Length (MM) Wide (MM) Thk (MM) CAT
HX9-G-ARD 1071 273 3.5 A
MYP-G-3RD 580 535 3.2 B
EPO-G-3RD 580 535 3.2 A
MYG-G-3R 966 350 3.2 A
MYN-G-3RD 649 530 3.2 A
GM SPIN-G-3FD 882 395 3.2 A
MY8-G-AR 880 400 3.5 B
GM2-G-AR 880 400 3.5 A

From this inventory data, one has to furnish customer orders based on specific dimensions demanded by them.  A typical Customer request would be to supply glass sheets as per the following dimensions

Length (MM) Wide (MM) Thk (MM) CAT
780 542 3.5 A

The firm may or may not have glass sheets of this specific size.  The objective is to identify glass sheets, from the inventory on hand, which match customer specifications.  If there is no exact match, then one must be able to obtain all inventory items which have the same Thk (MM) and CAT as the customer specified dimensions but the Length and thickness should be more than equal to the customer specified dimensions.  The length and width can then be trimmed to match the exact customer dimensions.  Furthermore, the result returned should:

  1. List only the Top 30 glass sheets available in inventory; and
  2. List those Top 30 glass sheets in ascending order of wastage (wastage caused when the glass sheet is trimmed to match the customer specified dimensions)

You may refer to my solution in this workbook.  I have shared two solutions - one using Excel formulas and the other using Power Query a.k.a. Get and Transform in Excel 2016.  Please read the Comments in cells F1, J9 and J16 of the "Solutions" worksheet.  The difference between the 2 solutions is:

  1. Formula driven solution - This is in range J10:AM14 of the Solutions worksheet.  This is a semi dynamic solution (as compared to the Power Query solution).  To get the models in ascending order of wastage, one will have to create an Area column in the base data and sort that column in ascending order.
  2. Power Query solution - This is in range J17:AM21 of the Solutions worksheet.  This is a dynamic solution.  Just change the customer specified dimensions in range G2:J2 of the Data and Query worksheet.  Thereafter just right click on any cell in the range below and select refresh.

Sum the largest 5 of the last 10 numbers in a row ignoring blanks

{2 Comments}

Assume a single row of data with numbers and blanks appearing at random intervals.  The objective is to sum the largest 5 of last 10 numbers in that row.  Solving this problem entails multiple steps:

  1. Identify the last 10 numbers in that row i.e. starting from the right hand side, identify the last 10 numbers
  2. Identify the largest 5 of those 10 numbers
  3. Sum those largest 5 numbers

Here are the steps

  1. Suppose the numbers and blanks are in range A2:V2
  2. Type 10 in cell X1
  3. Enter this array formula (Ctrl+Shift+Enter) in cell X2

=SUM(SMALL(IF((SUBTOTAL(2,OFFSET(V2,,,1,(COLUMN($A2:$V2)-COLUMN(W2))))<=X$1)*($A2:$V2)=0,FALSE,(SUBTOTAL(2,OFFSET(V2,,,1,(COLUMN($A2:$V2)-COLUMN(W2))))<=X$1)*($A2:$V2)),{1,2,3,4,5}))