Posts Tagged ‘Management’

Excel Tip: Largest values from Dataset (using Functions) July 8th, 2013

Vinod Kumar

If you haven’t read my post on “Excel Tip: SUM Top 5 values”, then this blog is an extension to that blog post. Do make sure to read it there because we will be using the same concept here in this tip.

Our dataset is simple as shown in figure below. We want to get the Max times for each city in a second list. How can we do this. The most easiest method I have seen is people resorting to PIVOT. If you want that implementation, do let me know and I will post that separately.

Just like in our previous post (Excel Tip: SUM Top 5 values), we used LARGE function there. Here we will use the MAX function with a conditional operator of IF. So in our example, we getting the MAX of values from the range B3:B8 where the value of D3 needs to be in the range A3:A8. Simple concept.

If you press a simple Enter you will get a value of 0. Remember the magic key (Ctrl+Shift+Enter) after you finish the formula and voila. You get exactly what you wanted. A typical output is below.

Now to copy this formula across E4 and E5. Find the cross arrow in E3 right hand- corner location. Now “Double Click” once you find the cross arrow.

Now that task automatically fills the range and you can see we have our desired output in less than a minute.

Hope you are enjoying the series of Excel Tips and tricks I am showing over the blogs. Do let me know of topics that will interest you and I will try to cover them here. Have a great day and learn something new every single day.

Continue reading...


Excel Tip: SUM Top 5 values July 2nd, 2013

Vinod Kumar

Since Excel Tips have been most sought after posts from a productivity part, I thought let me introduce an interesting tip in this blog post. Have you ever had a requirement to get the “Average of Top 5” or “Sum of Top 5” values from a list? What is the most common method that you used? I have seen most people use the sort function to order the values in descending order and just select the range of values to find SUM, Average and so on. So this post is a twist to that requirement. You can also apply the tip to other functions like Average which makes this post even more interesting.

Our  Dataset 

The dataset is simple. Seen below is a list of some random Sales figures and our requirement is to get the SUM of Top 5 Sales figures in D3 cell.

To start with, let us first find our top Sales figure first. This can be done using the LARGE function. The second argument “k” is to give the offset. Value of 1 means we need the top sales number. This is shown below.

The output can be seen as below:

Duplicate LARGE Function

Now that we figured out how to get the 1st, 2nd, 3rd and so on. We can just duplicate the function on the same range and make our SUM. It is as simple as it gets.

As you can see this for some reasons doesn’t look right. We seem to be duplicating and if we again look at the LARGE function. It takes just a value and doesn’t take a range.


Since SUM is taking in only values and not arrays. We need to find a way to pass some sort of array. Here we have SUMPRODCT function that takes in a set of array to work with. And to take a range of values top 5 (1+2+3+4+5), I have created a temp range with these values.

Super cool – Isn’t it?

What will happen if we do the same with SUM function?

You can see that for some strange reason the top 1 value was returned and it didn’t do a SUM for the array.


Here is the magic shortcut (CTRL+SHIFT+ENTER) – Edit our cell from above “=SUM(LARGE(B3:B10, C2:C7))” and press our magic shortcut.

Now there is something interesting that happens. Now the SUM of values is done for an array of values. The CTRL+SHIFT+Enter has many interesting use. Here I have shown one of them.

Since the array is shown using the {} – this doesn’t work if add the parenthesis manually. A small extension to this formulae is to add the array of {1,2,3,4,5} manually and then hitting our magic key CTRL+SHIFT+Enter also can do the trick.

Hope you enjoyed this tip and do let me know if you have used this in the past anytime. I will try to bring some more use of arrays in future posts.

Continue reading...


Why do you settle for the Ordinary? June 21st, 2013

Vinod Kumar

It is second to human nature to sometimes accept the ordinary and move on with disgust. Why do we do that? Is there merit in what we do? Are we thinking balanced? Are we biased in our opinion? There are multiple schools of thought and I thought to bring some to discussion in this post. Do let me know if these arguments make sense and have you ever experienced any in your lifetime.

When you stop being nervous is when you should retire. I’m always a little nervous for anything I do because when complacency sets in, that’s when I feel it’s time to move on to something else – Chris Jericho

Work delusion

If you are working, there will always be times when each one of us go through this feeling. Now in this argument there is no correct or wrong approach. Let me walk through this.

Assume you work in a services company. After a successful submission of project to client, you are requested to join another team on a completely different technology than what you have been working. Your passion for the technology is so high that your heart starts to reject the very thought that you are switching the platform. There is nothing wrong in it and your mind is playing games if you reject then you will be in bench and if you accept you will be switching competencies.

Now think about the whole scenario from a different angle. If the organization is paying your monthly pay-cheques, then isn’t it our duty to accept what our organization throws at us (logically)? Isn’t it our obligation to work for organizations goals keeping our emotions out? If we have to work on our own terms, why are we working for an organization and not starting our own company to work in our own terms?

Do you see logic to where I am taking this argument? Don’t try to just disagree and commit as you accept the ordinary. Try to change something. Both arguments are correct in their own way.

The arrogance of success is to think that what we did yesterday is good enough for tomorrow ― William Pollard

In my opinion this is falling into the trap of accepting the ordinary or the Okay attitude.

What to do?

It is tough to push our limits from not accepting the ordinary. It took me quite some time to get to terms in understanding this behavior – be on the lookout for complacent attitude and that is your first signs of accepting the ordinary. This obviously is going to pose us some interesting challenges and we need to work our way out.

When we are no longer able to change a situation, we are challenged to change ourselves – Victor Frankl

Ideally to achieve this, you need to be in the right frame of mind, with the right energy in your system while you enjoy each and every moment as they come at you. When you see life is just going Okay, there are a number of options:

1. Do something about it and Change it or

2. Improve your perspective to start enjoying what you are doing.

Understand, sitting around and complaining doesn’t change the perspective or the situation either. So they are the only two option you have. No doing anything is not going to get us anywhere. Look at things around you and take a strong stance of changing it or changing its perspective.

Just because everything is different doesn’t mean anything has changed – Irene Peter

Don’t be Complacent with:

  1. Your thoughts
  2. Your passion
  3. Your choices
  4. Your motivations
  5. Your Friendship
  6. Your Job
  7. Your health
  8. How you treat people and How you get treated
  9. How you want to spend your time now
  10. Your actions
  11. How you feel every single day
  12. Your decisions
  13. Your holidays and time with family
  14. Your relationships
  15. What you want to give others

This list can be endless. Don’t get complacent because you found enjoyment in other parts of your life. A negative attitude can rub into other activities that you do and can become detrimental to the task in hand. As I wrap up, accepting the ordinary is not good enough – go ahead and change something.

The man who never alters his opinion is like standing water, and breeds reptiles of the mind – William Blake

Continue reading...


Excel Tip: Splitting Column Values June 19th, 2013

Vinod Kumar

This is in continuation to tips around Excel. This particular tip has been around in Excel for ages. But just like any other feature in Excel this is yet another nice utility that is lesser known to many.

Let us assume you have the following data set. This is a simple data set and it has Name that has come from some external source. Now we want to split as Lastname and FirstName instead of the usual Name column as shown below.

Now to split the column, create a new column “C” by inserting a new column adjacent to our name column. Next select all the values from Name column. With this selected, goto Toolbar “Data” Tab –> “Text to Columns” under Data Tools section.

Selecting this invokes a wizard which is easy steps. Select “Delimited” and press “Next”.

This is the screen where we will select the delimiter. In our Dataset, the Name field has a space. So we will select “Space” in this screen. And select “Next”.

Now we will be presented with the final screen. This can be used for some Formatting options. In my data, I am going to select the defaults. Press “Next”.

Our final screen shows the split automatically done. The screen would typically look like below. Give the Column “C” as Lastname and we must be done.

This option works wonders when data values are consistent and has just two parts or three parts for ALL data values. Else we will get additional columns and if there is data, a popup appears telling us there are values and do we need to override. Be careful in such cases.

Hope you have used these in the past and I will try to bring in more such tips in our subsequent blog posts.

Continue reading...


Excel Tip: Art of Summing with Excel June 14th, 2013

Vinod Kumar

Let me bring this weeks Excel Tips and Tricks series with a simple tip. The scenario I am talking is most used – You want to Build a Sum of values for a table of values.

The most common requirement for any table of data is to build a SUM or Average along its rows and Columns. There are multiple approaches and let me outline some of the most common ways it is done today and I find each of these methods interesting and one method will always be efficient than the other. I am sure I have not outlined all the possible ways of doing this task.

Initial DataSet

This is a simple dataset of names and various Sales number. Now we need to have create totals Month-wise as well as employee-wise. So our dataset looks like below:

And we want to build SUM across and along as shown below:

Using SUM Formula

The basic old school of thought will be to Type “=SUM” as shown in figure below and we can give the range of C3-C6. Similarly we will do it for each of the cells.

Copy-Paste: A more efficient way to get the values across will be copy the Cell C7 and paste it across in D7, E8 and so on. This copies the formula of sum and gets the relative cells accordingly for each cell.

Using Shortcut

When the Cursor is at C8, Press “Alt+” (Alt and + key). This generally gets the adjacent cells and does the auto select with the SUM option. This is really powerful and your screen would look like this:

Now that we have got the C8, we can do Copy-Paste. Wait, now if you move your mouse to the C8 cell corner – you will be presented with a cross-arrow. Just drag this till F Column. It will magically fill the formula and make the SUM.

Now let me move to the Columns G3 and to get the SUM, let me use “Alt+” and press Enter. Voila, you have the Sum across columns done. Now we can use the same cross-arrow and drag – hold on.

Once you see the cross-arrow in columns, just double click. You will automatically fill till G7. Awesome right?

Using Toolbar

I am sure you have used any of these previous techniques in the past. The most efficient way I have figured out for Summing is using the Toolbar –> Home –> Editing Section –> AutoSum.

For this to work, select the region and just one click from toolbar. The values are filled.

It is just not only for single click SUM, you can also get AVERAGE, COUNT, MAX, MIN and many more functions as shown in figure below.

The best part of this method is, we have not used any Keyboard keys and I call this “Magic with mouse :)”.

Finally, I complete this weeks Excel Tips series and do let me know your feedbacks, I will be more than happy to write more on these lines and your comments are most welcome if you want me to write more of these in the future. I can surely write based on your feedbacks and enthusiasm for this topic. Office is an ocean and we can never be lost for tips to share. Thanks again for reading this far.

Continue reading...