In this simple 3 column dataset, there are 2 accounts – Konto 1 and Konto 2. Each account has a balance outstanding as on a certain date. However, if you notice carefully, there is no balance for any account on January 4-5,9-10 2020. The objective is show the balance outstanding every day. For days which […]
Read More →
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 →
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 →
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 →
{ 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 →
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 →
{ 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 →
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 →
{ 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 →
Show Balance outstanding everyday even if data for everyday is not available
{ 1 Comments }