Tags: VALUES

In a Pivot Table, show the most frequently appearing text entry by a certain parameter

{ 0 Comments }

Here’s a simple two column dataset Comment Identifier Intervals A 3pm-6pm A 9pm-12pm S 3pm-6pm S 3pm-6pm S 9pm-12pm A 9pm-12pm S 9pm-12pm D 3pm-6pm A 9pm-12pm A 9pm-12pm A 9pm-12pm A 3pm-6pm A 3pm-6pm For identifiers listed in column A, there are time intervals in column B. Note that for a certain identifier, a […]

Read More →

Alter the behaviour of a filter/slicer from OR to AND

{ 3 Comments }

Here is a simple two column dataset which shows EmpID in column A and DateWorked in column B.  My objective is to filter the table to show those employees who worked ONLY on August 17 and August 18.  I’d like to exclude employees who: Did not work on both these days; and Worked on both […]

Read More →

Quantify combination courses opted by students

{ 0 Comments }

Assume a dataset with two columns which lists down the student names in column A and courses opted for in column B.  Since one student can opt for multiple courses and the same course can be taken up by multiple students, there can be repetitions in both columns.  The objective is to create a matrix […]

Read More →

Compute month wise pending audits

{ 0 Comments }

Assume a three column dataset showing Audit ID, Date of receipt of audit mandate and Date of audit completion.  There are other columns as well but they are not important for our Analysis.  One may want to compute the following month wise: 1. Which (Audit ID) are the audits pending at the end of every […]

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 →

Perform an iterative sum of Top n values across multiple columns

{ 4 Comments }

A tournament has 18 participating teams with 25 players in each team.  Each team has to play five rounds of the Tournament and not all players play all rounds.  Scores earned by each player in each round are shown in individual cells.  If a player does not play a round, that cell is left empty. The task […]

Read More →

Count entries in a range which exclude certain user defined words

{ 5 Comments }

Assume the following sentences in range B2:B6 B2 – This is an Apple Pie B3 – An apple a day keeps a doctor away B4 – These Pears, apples and mangoes are sweet B5 – In this season, prices of mangoes have increased B6 – This is a glass of Guava juice In range B9:B10, type […]

Read More →