Tags: INDEX

Determine the top selling location for each product

{ 0 Comments }

Visualise a 3 column dataset as shown below – Location, Product and Sales.  Each location can have multiple products (Product A has Banana, Apple and Carrot) and each product can be sold in multiple locations (Banana is sold in locations A, B and F). The objective is to determine the location with highest sales for […]

Read More →

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

Read More →

Return the specific product which satisfies the user defined feature combination

{ 2 Comments }

Here is a simple matrix like data layout which shows the features available in every product.  Let’s assume that this data is in range A2:E8 (including the header row) Functions Product A Product B Product C Product D Function 1 x Function 2 x x Function 3 x x x Function 4 x x Function […]

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 →

Compute potential Sales of a retail outlet

{ 8 Comments }

The objective is to assist a Store Manager with computing potential sales across different products and colours.  To start with let’s assume two datasets: 1. Customer-Colour dataset – a two column table which lists down the colour preference of each customer; and 2. Colour-Product-Price dataset – a three column table which lists down the multiple […]

Read More →

Extract City, State and Pin code from an address string

{ 51 Comments }

For a lot of Excel users, a common problem is to extract City, State and Pin Code from an address string.  What compounds the issue is that there is no standardisation in an address string.  For e.g., one may end the address with a Pin code, while others may end it with a State and Country.  […]

Read More →

Prioritise investment liquidation to minimise Capital Gains

{ 2 Comments }

Assume a five column dataset with the Fund Name in column A, Market Value in column B, Gain/Loss in column C, Gain/Loss % in column D [column C/column B] and Type of investment in column E (Short term or Long term).  Since multiple investments can be made in one fund, there can be multiple line items […]

Read More →

Consider a Pivot Table Value field column as a criteria for computing another Value Field column

{ 8 Comments }

Assume a simple three column dataset showing hours worked by different machine on different dates.  So column A is Date, column B is Machine Name and column C is hours worked.  There are duplicates appearing in column A and B .  Blanks in column C depict machine idle time. The task is to create a simple […]

Read More →

Compute configuration count using Set Theory and Venn Diagrams

{ 0 Comments }

Assume a table which lists attendees for a Company’s Annual day function.  In this Table, data for every attendee is shown on a separate row so if an employee attends the function with his/her spouse and three children, then there will be 5 rows for that employee. The question is to determine the count of the following family configuration: […]

Read More →