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!!

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.

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

Power Of Sumproduct Formula in Excel

SUMPRODUCT is an excel function used to add the product of selected range of cells.

Syntax

=SUMPRODUCT(array1, array2,….,array_n)

First thought which comes up to an existing excel user is, why don’t we get the products of the values and use a SUM formula over it. If there are any criteria involved why don’t we use SUMIF / SUMIFS.

In order to clarify that, we need to first understand the advantages of SUMPRODUCT in place of SUMIF(S):

  1. In the SUMIIF(S) functions, while working between two excel files, source file should be kept open, whereas, in SUMPRODUCT it is not required
  2. OR logic not possible while using SUMIF(S) function, whereas, in SUMPRODCT we can use OR logic (Note: OR(+); And(*))

Let us see some practical example to understand the formula better. I have given below table which speaks about the training hours completed region wise, department wise and program category wise.

Scenario 1:

If you want to know the ‘Total training hours completed’, you need to sum up the product between program hours and programs attended. It can calculated using the below formula:

=SUMPRODUCT(D2:D10,E2:E10)

Scenario 2:

Let us say we want to calculate total functional training hours completed, in this case we have criteria to check, this can be solved using the below formula:

=SUMPRODUCT((C2:C10=”Functional”)*(D2:D10)*(E2:E10))

Scenario 3:

What happens when add more criteria, let us say we want to find total technical training hours completed by sales dept, you must use the formula with AND logic approach given below:

=SUMPRODUCT((B2:B10=”Sales”)*(C2:C10=”Technical”)*(D2:D10)*(E2:E10))

Scenario 4:

In order to find Total training hours completed by A and C region, you must use the formula with OR logic approach given below:

=SUMPRODUCT(((A2:A10=”A”)+(A2:A10=”C”))*(D2:D10)*(E2:E10))  

Click here to download excel file to practice at your end

Hope this posting has helped you to understand the power of sumproduct formula.

Happy Learning!!

MINA and MAXA formula

Max and Min formulas are familiar to most of us. Sometimes I wonder where to use formulas like MAXA and MINA.

This post is all about it. Hope this knowledge sharing will help you. Let’s start..

When you have a numerical column, where null values stored as text values like “-“ instead of 0, then MINA formula is the perfect formula to think about.

In the below screenshot image, I need to find the least in the dataset. Actually the minimum value in the dataset is the “-“ meaning null. MINA helps us to find that.

Similarly, when you have a dataset where you store result of criteria checking in the form of TRUE / FALSE, then this is the perfect place to find the whether any of the criteria check is TRUE in the dataset.

In the below screenshot image, I have described how MAXA is used to check whether any of the status has TRUE in it. If it has TRUE in it, the result of it will be 1. Further taking this value, I am deciding to go ahead or no go.

Click here to download the excel file