POWER OF COUNTIF FORMULA WITH WILDCARD CHARACTERS

Hi Folks,

In this posting we shall discuss how to use the wildcard characters “?“and “*“ in the COUNTIF formula to get some great results.

To understand this better, I have presented an example below

First formula will help us to get the count of the text entries alone

Second formula will help us to get the count of numerical values alone

Final formula will help us to get the count of those text entries whose length is 3 characters

If you use the COUNTIF formula with wildcard characters taking a formula column as a source range, then this behaves in completely different manner.

1st formula counts everything including the null entries returned by the formula.

2nd formula will count every entry excluding the null entries returned by the formula.

Hope you got some new ideas how to use the wildcard characters with COUNTIF formula.

Happy Learning!!

FINDING TOP 3 CONTRIBUTORS IN YOUR DATA USING INDEX AND LARGE FORMULA

Hi Folks,

In this posting we are going to understand how to use INDEX formula along with LARGE formula to extract the top performer details from your data table.

Let us take an example to understand this concept better

In the below table I got a list of salesperson names and their respective sales. In this list of data, I want to find top 3 contributing salesperson and their sales value.

Using LARGE formula to find the 1st, 2nd and 3rd largest values in the sales value column.

Using MATCH formula to find the position of these values in sales value column

Using INDEX formula to extract the salesperson names using the positions given by MATCH formula

Here comes the beauty when the formulas are put together. Amazing isn’t it??

Happy Learning…

 

Calculating Net Present Value Using NPV Formula

Hi Folks,

In this posting I am going to get into something which is financial. We are going to understand formula called NPV.

NPV stands for Net Present Value.

When should you use this formula?

When you want to compare two investments made in two different projects, this formula can tell us which is yielding better return.

You need to have a cash flow data from the start of the year when you made the investment, cash flow of each year should be also available. I have given you a screenshot to understand this better

First row in the screenshot shows the initial investment made for the projects. Cash flow for the remaining years are tracked and we are keeping the discount rate same for both the projects.

By keeping this data, we are using the NPV formula to find which project is yielding us better returns.

Syntax of the formula would

=NPV(INTEREST RATE, SELECT THE CASH FLOW VALUES(EXCEPT THE INITIAL YEAR)) + INITIAL YEAR INVESTMENT VALUE

If you closely the above screenshot, I have used the formula in the way which is explained above. We have the NPVs for both the projects.

Results are showing very clearly that, Project 2 is yielding better results than Project 1.

You can use this same formula to find out the better yielding investments who are promising same percentage of returns.

Hope this posting helped you in understanding how to use NPV at a basic level.

Happy Learning !!

Building Excel Custom Format Using Format Cells

Hi Friends,

In this posting we are going to discuss about how to use “Format Cells”, when you want to build your own custom formats for your values. Using FORMAT CELLS feature, you can change the format of the selected cell to any of the format from the available options like NUMBER, PERCENTAGE, CURRENCY etc.

If you want to build your format for your values, you should know the nuances of format cells.

Any custom format you want to build, you should define four inputs for it. You need to specify how positive value, negative value, zeros and text should be formatted.

Syntax would be

POSITIVE VALUES ; NEGATIVE VALUES ; ZEROS ; TEXT VALUES

Let us see few examples to understand this better;

In the above screenshot we have positive values, negative values, zeros and text values. Our requirement is, positive values should appear along with a word “BUY”, negative values should appear with a word “SELL”, zeros should appear along with a word “NO CHANGE” and finally for text values it should be appear as “NO DATA”

Selecting the RESULT column values in the above screenshot, go to “Format Cells” by using shortcut key CTRL+1 or you can simply right click on the selected cells and choose the option “Format Cells”

In the FORMAT CELLS option, go to the category “CUSTOM” and on the right-hand side of the dialog box under the word TYPE, clear the content in that box and start typing your custom format.

“BUY” 0.0 ; “SELL” 0.0 ; “NO CHANGE” ; “NO DATA”

Finally the values will start appearing in your required custom format, please refer the below screenshot;

If you closely observe this process, this changes only the appearance of the values. It will never disturb the actual value; these values can be used for any other calculations as well.

Give a try from your side to design your own formats for your values.

Happy Learning !!

Excel Formula for Dynamic Ranges with Offset and Match

Any excel formula which takes the range of cells as an input, we feed the range of cells by selecting them directly. What if those references has to keep changing based on your requirement? Changing references inside the formula manually will not be effective method.

OFFSET is one of the formulas which will help you to handle it. It becomes more powerful when it combines with other functions like MATCH. In the below example, the price of all the four products are added by using SUM formula.

In the above example, what if we want to add the price of any 2 or 3 products of user choice . This would become a very difficult task if the number of products is too many.

To make our job easy, here comes the feature of referring Dynamic Ranges with Offset & Match formulas.

In the below example, we have sales values for a period of twelve months. Now our requirement is to add the values pertaining to few consecutive months like Jan to Nov or Mar to Sep. This calculation is possible using Offset & Match formulas referring the Dynamic Ranges.

Below is the original table:

Now input Start & End in a column next to the table as below:

Now we are going to input the First month in the cell next to the Start Cell & Last month in the next to the End Cell. That is, if we need to add the values of the months from Feb to Nov, First month here is Feb and Last month is Nov.

Position of the month names is needed here to make our formula to work. MATCH is the formula which will help you to find position of a value in a list. For Jan it is 1, for Feb it is 2 and so on) is derived next to the month by using Match Formula as below:

The above Match formula throws the result as 2 for Feb & 11 for Nov.

Syntax of Match formula:

=MATCH (lookup_value, lookup_array, [match_type])

lookup_value: here is Feb,

lookup_array: is the list where we must check for the position of month names. In this case select the cells from A2 to A13

match type: we need exact match position of the month names, hence entering the value as 0.

Have a look at the below table:

Syntax of Offset Formula:

=OFFSET(reference, rows, cols, [height],[width])

reference – From which cell the offset must take place (Here it is the value pertaining to the month of January)

rows – number of rows to be moved to get the start month (Here the number of rows to move is derived by subtracting 1 from the match result of month name position (2-1 = 1).)

cols – number of columns to be moved to get the start month (Here the values are present in the same column where we have our reference cell hence value will be 0)

height – total no of rows to be fed (Here the total is derived by adding 1 to the difference between the positions of starting and ending month names (Feb & Nov : 2 & 11: 11-2+1 = 10))

column – total no of columns to be involved to reach the end month (As only one column is involved, the total is 1)

The offset formula will now add only those values pertaining to the months from Feb to Nov is same as the value derived by adding the values in a usual way. Refer yellow highlighted cells.

Now we shall try for different dynamic ranges by changing the Start & End months

Hope this posting has helped you increasing your knowledge on Excel. Happy Learning!!

Lesson 4 – Loading Unstructured Data in Power BI

In the previous post, we have understood how to load an excel dataset. In this post, we are going to go through a process of loading an unstructured excel data and converting them to a proper structured data.

When we use the term “structured data”, we mean by it should be report friendly at the same time, it should help me to derive to a meaningful decision out of that data.

We are going to see few options available in the Power Query Editor window which will help us to transform this unstructured data to a properly structured data.

We are going to start from the point, considering the data has been loaded. If you came to this post directly, request you to first read my previous post which speaks about loading excel data to Power BI desktop.

Now let’s start our journey..

Click here to download the excel file

Step 1: We need to go to Power Query Editor Window. In “Home” tab, under the “External Data” group click on the drop down “Edit Queries”, choose the option “Edit Queries”

Step 2: The previous step will take us to Power Query Editor window. Your screen would appear like screenshot pasted below

Step 3: In our dataset, we need to remove unwanted rows. We are going to first transform feature called “Remove Rows” under “Reduce Rows” group in “Home” tab.

Step 4: In our scenario, we need to remove top 3 rows, so we are going for the option “Remove Top Rows”. Now enter the number of rows as 3, as we need to remove only 3 top rows and click OK.

Step 5: First row in the dataset is our headers, we need to convert them from data record to header rows. To achieve this we are going to use next transform feature, “Use First Row As Headers” in “Transform” group under “Home” tab.

Step 6: Converting the month columns to rows in order to a comparative analysis. In order to achieve this we are going to use the third transform option called “Unpivot Columns”.

Step 7: Now select all the month columns, go to “Transform” tab, click “UnPivot Columns” option under “Any Column” group.

Step 8: We got two columns now, one with a name “Attribute” and other one with a name “Value”. We are going to rename them.

You can do it by simply right clicking on the heading and choose the option “Rename” or you can simply press “F2” key. We are going to rename them “Month” and Other column “Sales”

Step 9: Last part of our work, removing unwanted column from our dataset. In this dataset, the “Grand Total” column is not relevant after unpivot is done. To achieve this we are going to use the fourth transform feature called, “Remove Columns”.

Step 10: Select the “Grand Total” column in the dataset, click on the “Remove Columns” in the “Manage Columns” group under the “Home” tab.

Now what we have is a perfect dataset which I can take it further for my analysis work or a visualization requirement.

Step 11: To save and load the transformed data to power BI, you need to close Power Query Editor window and return to Power BI desktop window. To do it, click on “Close & Apply” option under the “Close” group in “Home” tab.

Additional Tip: All the transform steps are captured under the “Applied Steps”, any time you can revert to any point in our data by simply deleting the particular step.

In the next post, we shall discuss more on Data transforming options. I hope this post gave you some idea about using basic transforming options in Power Query Editor

 

 

Rounding Values Using 7 different Excel Formulas

When you are developing a formula for any calculation, and if the accuracy depends even on the decimal points of the result, then it is important to know options available for us to round off the values in Excel.

“Increase Decimals” / “Decrease Decimals” / “Format Cells”

This option is available in “Number” group under the “Home” tab. This option simply changes the appearance of the value as if it is rounded, but, the value remains the same.

In the below screenshot, the number 4.567 when rounded off to an integer, you will get 5. Now we shall see how the option ‘Decrease Decimal’ changes its appearance but not actually rounding it off:

When you double click on the cell or when you look at the formula bar, you can observe the values is not rounded off at all.

If your requirement is to display like rounded off, this option is perfectly fine. When you want the rounded value for any calculations then we need to see what other options we got.

Let’s start our journey, in order to get the actual round off values, we have few functions in Excel as mentioned below:

  1. ROUND
  2. ROUNDUP
  3. ROUNDDOWN
  4. TRUNC
  5. MROUND
  6. CEILING
  7. FLOOR

Firstly, we shall investigate the function ROUND.

The syntax of the formula goes like this  =ROUND(number,num_digits)

In the above syntax first argument ‘number’ is the actual value which needs to be rounded off and the second argument ‘num_digits’ is the number of digits required after the decimal point.

In the below screenshot, we are using the formula to round off the value by 1 decimal point.

This formula will check whether the second decimal value is greater than or equal to 5, if it is increases the first decimal value by 1 otherwise it will be retained as it is.

The above table is self-explanatory. The requirement for the first value is to round off to one decimal point, for the second value it is two, for the third value it is three and for the last value it is only the integer without decimal values.

Let’s go to the second formula, which is ROUNDUP

ROUNDUP function adds one (+1) to the value required decimal place irrespective of whether the consequent value is greater than / less than / equal to 5. Below table is for your reference:

Look into the second value – 2.123 – Third point to decimal is less than 5, however, it adds one (+1) to the previous value – 2 and makes the number as 2.13 instead of 2.12

Let’s move on to third formula, which is ROUNDDOWN

ROUNDDOWN function retains the same value in the required decimal place irrespective of whether the consequent value is greater than / less than / equal to 5. Below table is for your reference:

In the above example, have a look at the first value 4.56, here the second decimal value is greater than 5, however, by default it retains the same value – 5 and makes the number as 4.5 instead of 4.6

Now the fourth formula, TRUNC

TRUNC function truncates (shortens) the number by removing unwanted decimal places in the value. This formula will not round off your value.

The syntax of the formula is =TRUNC(number, [num_digits])

First argument number is the actual value that needs to be truncated. num_digits is not a mandatory argument. It is the number of digits required after the decimal point.

Let’s have a look at the below screenshot to understand this formula better

Now the fifth formula, MROUND

 

MROUND this formula will help you to round the value to a desired multiple. Normally when we say round off a value the increment or decrement in the values happens by 1 but not in 3s or 5s or 10s. So when you want to round a value in 10s or 20s or 5s, this formula is better choice for us.

Syntax – =MROUND(number, multiple)

For instance, when I want to round off my actual values in the multiples of 6, if I feed the number 5 it will be rounded off to 6.

Note: This function gives the result as the next nearest multiple, if the actual number is greater than or equal to half of the multiple given. Else, it gives the previous nearest multiple.

CEILING always returns a number rounded to next desired multiple.

Syntax – =CEILING(number, significance)

The argument – significance is your desired multiple

In the above example, the value 7.22 is rounded off to 14 as the multiple is given as 7. Logically, the rounded off value should be 7 as the difference between actual number and the multiple (7.22-7 = 0.22) is less than 3.5 (half of the multiple). But the CEILING function always gives the next multiple as the result.

 FLOOR always returns a number rounded to previous desired multiple irrespective of the difference between the actual number and the next nearest multiple, whether it is greater than/less than/equal to half of the multiple given.

In the above example, the value 4.56 is rounded off to 0  as the desired multiple is given as 5. Logically, the result must be 5 as the difference between the actual number and the multiple is greater than

 Hope this posting helped you in understand different options available in excel for rounding off a value in any excel calculation.

Lesson 3 – Loading Excel Data In Power BI

Hi Folks,

In this post we will understand how to load our data in to Power BI desktop for the analysis. We are considering an excel data source here for this posting, and let’s get in to the process of uploading excel data file to Power BI desktop.

Let us start the process

Step 1: Open Power BI Desktop in your computer, sign in to your Power BI account.

Step 2: Go to “Home” tab, under the “External Data” group click on “Get Data” button, Choose “Excel”

Step 3: Locate your excel data file and click “Open” in the dialog box

Step 4: Select the sheet name from which you want to extract the data, click on the “Load” button. That completes our process. Now you can observe on the right hand side area of Power BI desktop, all the fields of excel file will be listed and it is now ready for summarization and analysis.

Step 5: In case if there is any issue on the data load, it is better to click “Edit”, correct the issues and then load it to the Power BI. We shall see now what options will be available when you go to “Edit” mode.

Step 6: On the “Home” tab, clicking on “Edit Queries” icon, will take us to “Power Query Editor” window. This is the place, where you have lot of tools and features to handle issues when you load an unstructured data.

Step 7: Every step you took while loading the data is captured in Power Query Editor, available under the “Applied Steps” area. Those steps can be renamed or more information can be added in the details properties of those recorded steps. This feature is termed as “Self Documentation”.

In the future posting we shall see more about the Transform tab features.

Creative Stacked Bar Chart

In many dashboards and MIS reports, we all are looking for some innovative excel charts or impressive excel charts. This visualization I came across last week when I was going through LinkedIn postings. Why don’t we create something like this.

Now we shall see how to create the creative stacked bar chart.

You can download the excel file here

We are going to consider the below data for creating the chart:

The normal way of displaying the above data in a chart is mostly in the form of bar chart as below:

The above chart can become more explanatory if we add some more values to the same. This is where the Creative Stacked Bar chart comes into picture.

Step 1 – Insert an additional column between the two columns – Parameter & Cost and we shall name it as Grid. Enter the same value under the Column – Grid so that the length of the stacked bar remains the same.

Step 2 – Select the data and insert the Stacked Bar Chart

Step 3 – Add data label as below:

Step 4 – Now we need to replace the constant value ‘2000’ with the team’s name. Click on the values (2000) and right click, go to format data labels. Uncheck the Options – Value & Show Leader Line and check the option – Category Name

Step 5 – Alignment of the values/names inside the bar can be done using the ‘Label Position’ – Inside End – Right Alignment; Inside Base – Left Alignment (Font size can be adjusted as per the user’s choice)

Step 6 – Gap between the bars can be adjusted using the option – Gap Width below series options under Format Data Series – In this chart I am going to keep the gap width as 40%

Step 6 – Removing unnecessary items in the chart, like grid lines, axis labels and legends. This can be done, by simply click on them and press the delete key in keyboard.

Step 7 – Font colours can be adjusted as per the user’s choice to have a better visual effect

Step 8 – Now comes the best part of the chart. In order to display the values in a better way, we are going to use a shape as below:

Remove the outline for the shape and give a lighter colour for the shape and also the gradient effect as below:

Copy the shape and select the bars with values & paste the shape:

Now our desired output of creative stacked bar chart is as below:

 

Power of UnPivot in Power Query

Unpivot is a process in data transforming. This is present inside the Power Query Editor window. Using this feature, we can transform the data to the required structure when they are not in the proper format.

 Let us take a scenario to understand this feature better, imagine you have 3 years of sales data (Monthly Product Sales) where the data has been managed in such a way month names are kept as headers and product names are row wise. Check out the screenshot below.

Problem in this arrangement I cannot do even simple filtering for a specific quarter or year, because horizontal filtering is not possible in excel.

Another problem is when I analyze this data to compare year wise or quarter wise sales for the product, this data structure will not help.

So I need to transpose this data in order to use it for my requirement. Paste special transpose will not solve my requirement. Hence using the Power Query to resolve this issue

Click here to download the excel

Steps involved in UnPivot Data Using Excel Power Query

Select your data and go to “Data” tab and click on the option ‘From Table/Range’ which is a part of ‘Get & Transform Data’ group

A dialog box gets opened with the range of cells as below: Check whether the data has been completely and “My Data Has Headers” option should also be active.

Click OK.

This opens the “Power Query Editor” window and you can see your data has been loaded in.

Now start selecting entire month columns in the loaded table.

You then need to go to the “Transform” tab and select “Unpivot Columns”.

Now the magic happens, the data transforms beautifully and becomes very report friendly

In order to use this transformed data, you need to load this table back to excel.

Go to the “File” tab and choose “Close & Load”.

Process completed