Tags: IF

Show text entries in the value area section of a Pivot Table after meeting certain conditions

{ 0 Comments }

In the value area section of a normal Pivot Table one can only show the result of aggregation functions such as SUM(), COUNT(), AVERAGE() etc.  Even if one drags a text field to the value area section of a Pivot Table, one cannot show those text fields because they automatically get counted. Consider the following […]

Read More →

Count tasks by status

{ 0 Comments }

Assume a simple 3 column dataset as shown below – the date of each task and the status of that task. The objective is to get the status wise count of tasks by the last time stamp.  So for the Status “To-do”, the count should be 2 – Task ABC and DEF.  Only these two […]

Read More →

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 →

Segment towns according to volume contribution and market share

{ 0 Comments }

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 shared above, the desired result is shown in the screenshot below: The desired result is shown in range E16:E19 and […]

Read More →

Calculate rolling sum for the past week by ignoring blank cells

{ 0 Comments }

Assume a simple dataset as shown in the image below (the input data is in columns A and B only.  The desired outcome is in columns C and D). The objective is to calculate the 7 days rolling sum and average (as shown in columns C and D) ignoring blank cells.  So in cell C8, […]

Read More →

Compute hours spent on projects given resource allocation

{ 2 Comments }

In the dataset below column A has the Employee Name, column B and C are the assignment start and end dates, Column D is the location and columns E to J are the Month-Year columns.  So each row represents data for an employee on a particular project.  The numbers in range E2:J8 represent how much […]

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 →

Analyse free flowing text data or user entered remarks from multiple perspectives

{ 0 Comments }

Here is a 2 column dataset – UserID in column A and Remarks in Column B.  This dataset basically tabulates the remarks/comments shared by different users.  Entries in the Remarks column are basically free flowing text entries which have the following inconsistencies/nuances: Users reported multiple errors which are separated by comma, Alt+Enter (same line within […]

Read More →

Show Project wise status in a Pivot Table

{ 0 Comments }

Visualise a simple 6 column Table as shown below – Project Name and the finish date for each of the 5 stages that the projects go through.  Each project goes through 5 stages – Requirement (Req), Development (Dev), UAT, Implement and Warranty. The objective is to report on the status of each project at the end […]

Read More →

Determine the lowest bidding vendor(s) for each product in a Pivot Table

{ 0 Comments }

Imagine a dataset like this.  This dataset shows vendors that submitted proposals for supplying various parts to a Company.  There is one column for each of the twelve months. Via a simple Pivot Table, one can determine the lowest bidding vendor per product (part) for any chosen month.  However, one may also want to know […]

Read More →