Most powerful yet unsung Excel functions for Compensation professionals

I thought about writing a more technical post but didn’t know where to start from.
It could be a nice series of post starting from scratch, however after some googling, I thought it might be more beneficial to write about the unknown and/or unrecognized magic words that saves your time in front of the screen covered with cells.

As I advance in my career in Rewards area, I started to use macros more and more. This could be a good subject for another post but there are some formulas or that saved my life.

I will list some of these Excel functions and give you examples how and when to use it. You will not find the generic VLOOKUP() and similar essential formulas in the following list. Here we go:

INDEX() and MATCH() combination

Slowly leave your VLOOKUP() down and kick it to me. Nobody needs to get hurt!

Yes, this is it. The combination of INDEX and MATCH is one of the most powerful formula combination to save your life while trying to extract data from a data table with multiple input variables.

Let’s say we have a table like the one below and we would like to get the “Benefit” amount of the employee with the ID of “EMP3”:

MS Excel INDEX/MATCH combination

The formula would look like this:


As you can see, the index formula is the main formula where you need to identify as a first variable the area where the values are stored. You should not choose the left and top column in this area hence they are identifier row and column which will help us in the second and third variable.

INDEX() function works this way: I will give you the row number and column number and the cell at the intersection of those is the value I want.

MATCH() function a little bit different: Give me the exact place of this value in a range/series.

So with the MATCH() functions we identify the row and column numbers that INDEX() want. We want the row number of EMP3 in unique ID’s and the type of the pay “Benefit”.


Life cannot be without errors. More important thing is how you handle the errors. This formula can give you a better control in your data management if used properly.

Let’s say, you want to pay 5,000 USD even there’s an error in the calculation (in this example addition of three cells) of a cell.  Normally, the formula would look like this:


What if K7’s value is not a number? The Excel would give you an error for sure. To avoid seeing “#….!”, you can use IFERROR() and tell that even if there’s an error my sum would be 5,000:


Pretty simple, huh?

AND() and OR()

The basic logical operators are here. If you want to check two conditions that they are TRUE at the same time (AND) or at least one of them (OR) you can use these:

=AND(“Arif” = “Arif”, “Excel” = “Excel”)

will always give you a boolean result of TRUE like

=OR(“Cat” = “Dog”, (3-2) = 1)


Now, before going forward I would like to warn you about the following two functions. Although they are saving lives in some instances, they are called as “volatile functions”.
A Volatile Function is one that causes recalculation of the formula in the cell where it resides every time Excel recalculates. This occurs regardless of whether the precedent data and formulas on which the formula depends have changed, or whether the formula also contains non-volatile functions. That’s why you have to be really careful and mostly avoid using this.


We will use the same table above. As per that data table, we will analyze the below formula and see how OFFSET() function works:


Translation is: We will start from C4, go 4 cells down, then 1 cell right and starting from that cell we take all 6 values in a 3×2 table and add them all… Makes sense, right?

The table is this part:


So, the result would be in this case 37,594 in case you want to try.


I use this mostly in consolidation of various files with pre-set names that can be linked to an identified variable.
For example, you have one template for each country in your region and you want to get a value from its first sheet, cell A1:


It might be confusing, but the above figure is pretty self-explanatory. Please note that, you will see a #REF! error, if the target Excel file is not open.

I will continue to explain other functions in the Part II of this post and hope that this will help you come up with a better Compensation analyses!

People Analytics and Human Resources

Nowadays, the fanciest topic in Human Resources field is People Analytics. The word “People” comes from the area of Human Resources and the word “Analytics” from Statistics, specifically Predictive Analysis.

The evolution of decision-making in HR is now on the verge of shifting from reactive to a proactive position. The main reason behind it would be the changing dynamics in the working environment and the good, old “talent war”. The traditional HR decision-making process excluded the data analysis until the last decade. It was fascinating; Human Resources was also becoming a true co-pilot where the decisions were backed with fancy graphs and some numbers. But what was the source of data? Past.

We are now in an era where we start trying to become well-recognized fortunetellers. The success lies within the capability of reading the future based on historical and current events while assessing and implementing current factors that could affect the situation.

So, the summer days of “Analytical HR” is over. It’s becoming obsolete. HR needs to be geekier, more technology-driven, more complex than ever.

We need more “people engineers”, statistician or mathematicians in HR department rather than some social guys/ladies that are competent in arranging events or shoot out some fancy corporate announcements  to become more successful. No puns intended!

I just wanted to remind you that the world’s changing.
So should the beloved department of Human Resources.

P.S.: I will go into detail in another post what would it mean especially in our area, Total Rewards.

Working under Stress?

It’s been so long since the latest blog post but I’m sure you can excuse me if you ever have an idea of the “Rewards peak season”. It’s going off, so expect more activity here!

Back to what title says – if you think you’re under pressure at work, please think again after watching the video below. Just saying…

Is this the end of Merit Pay increase?

It’s been awhile that I couldn’t spend time on my blog, but after reading this month’s WorldatWork Journal, I wanted to share some thoughts regarding the merit pay.
I feel that it’s a good timing to question merit increases hence it’s the time for pay reviews here in Dubai.
As per the article authored by Scott, Somersan, Repsold in this month’s issue, I quote a paragraph:

Low merit pay increases in recent years have made it difficult to differentiate the rewards of high performers from those with only average performance. Furthermore, getting managers to differentiate performance across their employees is difficult. One strategy for improving merit pay that has been discussed in recent years is to separate pay adjustments based on labor cost increases from increases for good or exceptional performance. The former would be based on the cost increases in the labor market not on employee performance. Thus, merit increases that were previously rolled into base pay would be given as bonuses, which does not increase base pay and long-term fixed payroll costs.

I would like to concentrate on the last sentence. I’ve experienced very high and also very low levels of inflation in some countries. As the authors also implied, it was easier to link the merit increases to inflation and cost of living and labor in recent years. However, especially in emerging and developed countries, trying to differentiate employees with merit pay linked to inflation started to be an issue because of low increase rates.

An example:

A very aggressive merit matrix set with regard to (of course) corporate costs and budgets would have maximum differentiation of 300% -tell ya, it’s a VERY aggressive one- between a top performer and low performer.
Multiplying this with a very low merit increase like 2%: Max. differentiation is only 6%?

Here it is. A basic example showing that merit pay is not enough anymore to be a differentiating factor for the high performance because I don’t think it’s rational to tell a high performer that he/she is differentiated from a low performer by only 6% of the annual salary. It sounds funny, I know.

In the new world’s economic outlook we need to revise our reward schemes and be more flexible and creative. We have the tools and practices – just need to use them accordingly and smart.

Last but not least, I think it’s a good idea to rely less on merit increase and try to differentiate employees with a short-term incentive where we also need to leverage on our performance management systems and processes.

Let me know what you think about this topic by writing a comment to this post.

What should you expect from your C&B colleague?

It’s nice to see that nobody’s knocking on others’ doors in the festive season; so many people are having very relaxed time at the end of the year.
In Middle East, the general practice is that short-term incentive payout and the salary reviews are generally done in the first quarter of the year, so it applies for the C&B professionals here as well.
Well, this might be the silence before storm (Yes, it is!) but it’s the subject of another post.

We mentioned knocking on doors… The dark, unholy and rusted doors of the narrow cave called C&B Department?
Hey! Chill… Just joking… We’re not Orcs…
Image courtesy by Iron Mitten

However, I understand. Since many years, C&B has been the black box of the organization and the C&B professionals are seen as the bad guys of the town. Employees were very nervous to talk to them or ask questions… They were afraid to get a call from them which might mean that their time in the organization is over…

But it’s not the case in reality and I blame us for this.  We created this dark environment by being secretive, discussing the matters behind closed doors and doesn’t come up with answers when even the most simplest questions like “how are the salaries reviewed?” are asked.

Flickr_IntroverLeadersCommunicationMED-kenfagerdotcom4886342418_db75a10808Since last decade we are investing on the importance of the strategic communication in Total Rewards and try to be more transparent, educative and approachable. We are saying that three main objectives of Total Rewards approach are to attract, motivate and retain the employees and we are working on that.
We are spending our efforts more and more on how to communicate rather than how to calculate.
This has been accomplished in some regions and it’s still in process in others.

That makes me feel we’re in the good way when I see the happy sparks in the eyes of the employees I train on Total Rewards.

Do the C&B professionals to be that much approachable and transparent? What do you need to expect from them?

I think that there’s a limit. Yes, we should be more open and social  but we cannot give up from the main characteristics our job requires:

  • Confidentiality

A C&B professional is not allowed talk too much. At least about the details of his/her job. As a person having the maximum access to the most sensitive data of the organization, he/she cannot spread even a single part of that data. Luckily, many C&B professionals follow this invisible oath.

What do you need to do:
You shouldn’t ask too many questions regarding other people or any plans and projects about organization.

  • Working under pressure

We work under pressure.
Yes, everybody does but we do  a bit more. We work with Human; we work with Government; we work with Blue-collar and also Executives; we work with past, present and future money transactions which goes directly to YOUR pocket rather than the bank reserves which has a direct impact on people’s lives. We work with transactions as well as strategic plans.

What do you need to do:
Do not blame us for issues and problems directly and have a co-working approach. We work in the same organizations and we support you; so should you. Please do not think that C&B is just printing some pay slips and transferring money via online banking.
We can prove easily that we conduct analytical practices that many engineers never do in their daily routine. (No offense; I gave this example because I was an engineer before and never done such exhaustive analysis before)

  • Influencing

We are your consultants. If your C&B Specialist is a very well-educated and experiences in his/her area, she can be very influencing on you and your decisions.
Personally, I helped many employees when they got another offer from another company and we cannot retain them anymore (after the real break-up you can stay friends, no?), when they had issues with tax authorities or even when they were confused about the host package for their expatriation. So, again, we’re here to help.

What do you need to do:
Consider us your therapists in your Rewards. We’re there to help but also be fair and consistent. We can give you insight about the information that you can only have by word of mouth (which is almost always untrue) or won’t have access at all. Think about it like you have a legal issue and you go directly to the Corporate Legal Counsel.
Same thing here… Just don’t misuse this service and don’t take our recommendations as we try to damage your career – if we say so, it’s real.

I assume that if your C&B professionals and employees work together taking above points into consideration, the organization’s journey in Strategic HR Communication will have a big leap sooner than you think.