Author: Ashish Mathur

Compute standard hours spent on weekdays by Tier, Week, Month and Country

{ 0 Comments }

Imagine a Sales dataset with the following columns – ID, Country, Start date, End date and Tier. Here’s a snapshot of the table: ID Country Start date End date Tier 33948 ES 25-Sep-17 28-Sep-17 3 19820 US 08-Oct-17 17-Oct-17 4 9118 US 27-Oct-17 03-Nov-17 2 1563 ES 02-Sep-17 07-Sep-17 2 11087 US 18-Oct-17 27-Oct-17 1 […]

Read More →

Determine cumulative interest payable on an annuity with varying time periods

{ 0 Comments }

Imagine a fixed monthly amount due to an Organisation for services rendered to various customers.  While an invoice is raised every month by this Organisation, not all pay up the dues on time.  For unpaid dues, the Organisation charges its client interest ranging from 3% to 9% per annum.  The objective is to determine cumulative […]

Read More →

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 […]

Read More →

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 […]

Read More →

Sort individual columns of a Pivot Table based on a slicer selection

{ 0 Comments }

Here is a simple four column dataset Week Team User Codes 1009-1016 Default-LossMit FAST INTL\KOrdillano ATPD/5 1009-1016 Default-LossMit FAST INTL\KOrdillano ATWI/116 1009-1016 Default-LossMit FAST INTL\KOrdillano ATWI/3B 1009-1016 Default-LossMit FAST INTL\ADulnuan ATWI/116 1009-1016 Direct – HSD INTL\JCustodioii S/2 1009-1016 Default-LossMit FAST INTL\abacud ATWI/116 1009-1016 Default-LossMit FAST INTL\SCaparon ATWI/116 1009-1016 Default-LossMit FAST INTL\ADulnuan ATWI/116 1009-1016 Default-LossMit FAST […]

Read More →

Generate a list of assignees for different projects based on a competency matrix

{ 4 Comments }

Here is a small sample of a Project matrix which shows tasks to be accomplished for various projects.  There can only be upto 6 tasks per project. Project Name Task1 Task2 Task3 Task4 Task5 Task6 Project1 Painting Chef Gardener Project2 Tiling Digging Engineering Project3 Mechanic Engineering Here is a competency matrix showing the competencies of employees on […]

Read More →

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: Identify the last 10 numbers in that row i.e. starting from the right hand side, identify the last 10 numbers […]

Read More →

Discover insights with the “Sand Dance” visual, query your dataset with Natural Language Queries and Cortana integration

{ 2 Comments }

This post is in continuation of an earlier post where I applied Excel’s Business Intelligence tools (PowerPivot, Power Query and PowerView) to analyse the Sales data of an E-Commerce Company.  So, just for starters, in that post, I have basically sliced and diced the Sales dataset of an E-Commerce Company from multiple perspectives/facets to know the […]

Read More →

Convert a text entry into its number equivalent

{ 0 Comments }

Consider this simple two column table showing text entries in column A and the corresponding numbers in column.  Assume this data is in range A2:B11 (headings are in A1:B1). text Value A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 0 The objective is to […]

Read More →

Compute an average for the same day in the past 3 years

{ 4 Comments }

Assume a simple two column dataset with dates in column A and numbers in column B. The dates in column A are from January 1, 2013 to December 31, 2016 and numbers in column B are for the period January 1, 2013 to December 31, 2015 (there are no numbers for January 1, 2016 to […]

Read More →