Tags: RANKX

Segment towns according to volume contribution and market share with a slicer

{ 0 Comments }

This post is an extension to the one I posted here – Segment towns according to volume contribution and market share. Here’s a simple dataset of Shampoo sales in the state of Rajasthan, India. For a chosen segment, one may want to segment the 4 towns based on the following conditions: Based on the two screenshots […]

Read More →

Compute Relative Size Factor per vendor

{ 2 Comments }

Relative size factor (RSF) is a test to identify anomalies where the largest amount for subsets in a given key is outside the norm for those subsets. This test compares the top two amounts for each subset and calculates the RSF for each. In order to identify potential fraudulent activities in invoice payment data, one […]

Read More →

Perform an aggregation on Top x items after satisfying certain conditions

{ 0 Comments }

Visualise a 5 column dataset as show below.  This is a very small sample of the actual dataset.  It shows the date on which supplies were received for each item from Vendors and whether those supplies had errors in them.  Finally those identified errors have been bucketed into relevant categories.  The Item ID# is a […]

Read More →

Compute the average of values against the 5 most recent dates of each Category

{ 0 Comments }

Here is a simple 3 column dataset showing Categories, Date and Value Catagorie Date Value Fish 08-12-2015 6 Crab 05-12-2015 7 Crab 04-12-2015 6 Bird 27-11-2015 4 Snow 25-11-2015 10 Cat 21-11-2015 7 Dog 12-11-2015 5 Dog 28-10-2015 5 Fish 12-10-2015 3 Bird 11-10-2015 9 Dog 22-09-2015 9 Crab 17-08-2015 8 Cat 11-08-2015 1 Fish […]

Read More →

Filter the Rank Field in a Pivot Table

{ 4 Comments }

Imagine a two column dataset – Customer Code and Material Number (with alphanumeric data).  The objective is to determine the second highest quantity sold per Customer code. Since we will first have to determine the Customer wise and Material Number wise quantity sold, a good way to get started is to use a Pivot Table.  […]

Read More →

Rank numbers in a range after satisfying conditions

{ 0 Comments }

Assume a five column dataset – ID, Age, Gender, Time and Class.  For chosen ID’s, the objective is to: 1. Assign a Rank (in ascending order of time i.e. lowest time will be rank 1 and so on) to each ID 2. Determine the overall place of each ID – Count of unique time entries […]

Read More →

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

Read More →

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

Read More →

Sum highest n numbers based on conditions

{ 4 Comments }

Assume a two column database with names in column A and numbers in column B.  Names in column A may be repeated.  If a user types a certain name in a cell, a formula should sum the highest three values from column B for that name. Depending upon the version of MS Excel which you are […]

Read More →