# Category Archives: Formulae

How to communicate with Excel through formulae to make it do what you want.

# XL FORMULA FOCUS: COUNTIF to Calculate Headcount

No HR Dashboard is complete without a headcount summary, and Excel has given us the COUNTIF function to make it easy!

It’s a pretty straightforward formula – this is how Excel describes it: “Counts the number of cells within a range that meet the given criteria”. So all you need is a range of cells and the value that you are looking for within that range. For example if you had a list of staff and their department, you could count how many are in your Finance team.

Here’s what it looks like:

COUNTIF ( range , criteria )

So if your table has a column with departments listed, that’s your range – let’s say it’s C2:C14. If you want to count how many of those say Finance, you can put “finance” as your criteria (don’t worry, it’s not case sensitive”). Your formula would look like this:

COUNTIF ( C2:C14, “finance” )

If you’re using this formula for a headcount summary on a dashboard, you’ll want to show more than one department, in a table something like this:

If that’s the case you won’t want to keep changing the criteria part of the formula in each cell, like this:

COUNTIF ( C2:C14, “finance” )

COUNTIF ( C2:C14, “HR” )

COUNTIF ( C2:C14, “IT” )

COUNTIF ( C2:C14, “Marketing” )

COUNTIF ( C2:C14, “Sales” )

Instead, you can just reference the cell to the left, in the Department column, and make the range part of the formula a static range by adding \$ signs (you can just press F4 as a shortcut) like this:

COUNTIF ( \$C\$2:\$C\$14, E2)

Note that the we haven’t made the criteria reference static as we want this to follow down column by column to change the department.

Here’s what it would look like:

Of course on this small table you can quickly count things yourself, but when you have a table with dozens or even hundreds of rows the COUNTIF function can save you an awful lot of time.

# XL FORMULA FOCUS: ROUND, ROUNDUP, ROUNDDOWN, MROUND (an introduction to Excel rounding functions)

Here is a quick introduction to four straight-forward rounding functions in Excel. We’ll look at each formula in the picture above and how they help you to round numbers.

ROUND(number, num_digits)

This is probably what you think of instinctively when you hear the word “rounding”, and so this might be the formula you use most often. In the example above we’re rounding to two decimal places, so if the third decimal place is 5 or above we round the second decimal place up, and if the third decimal place is below 5 we round the second decimal place down. If num_digits is 0 then it rounds to the nearest integer, and if num_digits is negative the number is rounded to the left of the decimal point.

ROUNDUP(number, num_digits)

You’ll start to see a theme emerging here – all of these rounding functions follow the same syntax (number, num-digits). So if you understand ROUND then ROUNDUP should be fairly intuitive for you. But just to spell it out, in the example above we’re rounding to two decimal places, so if the third decimal place is above 0 it rounds the second decimal place up, otherwise it stays the same.

ROUNDDOWN(number, num_digits)

Again the same format, only this time we’re rounding down. In the example above we’re rounding to two decimal places, so regardless of what the third decimal place is, we’re chopping it off and the second decimal place stays as it is.

MROUND(number, num_digits)

MROUND, or Multiple Round, rounds your number to the nearest multiple of your choice (guess what – same syntax!). So above we decide to round to the nearest 5, and it uses “standard” rounding and returns the figure up or down to the nearest multiple of 5.

That was your whistle-stop tour of Excel’s most straightforward rounding functions. Thanks for listening!

# XL FORMULA FOCUS: TODAY, YEAR, MONTH, DAY, DATE (an introduction to Excel date functions)

This post will teach you five Excel functions to help you deal with dates. We will work our way through in the order that they are shown in the picture above.

# TODAY

=TODAY(). That’s it. That’s all you have to type, the brackets are empty. And it gives you today’s date, updating accordingly over time (whenever the worksheet recalculates). This makes it great for keeping things like age or length of service up to date.

# YEAR, MONTH, DAY

You just point these at a cell containing a date (in the picture we use the date returned by the TODAY function). These functions then return the applicable data about that date. For example, if your date in C1 is in 2013 and you put =YEAR(C1) it will return 2013. If C1 contains a January date, =MONTH(C1) will return 1 (ie the first month of the year). And if you enter =DAY(C1) where the date is the first of a month, it returns 1 (note that this refers to the day of the months rather than the day in a week (which you can use WEEKDAY for).

# DATE

So DATE works kind of like the opposite of YEAR, MONTH and DAY:
=DATE(year,month,day)
If you tried this =DATE(2013,1,1), it would give you 01/01/2012 (or 41275, the Excel serial number that represents that date). So you can see the example in the picture above goes full circle – it extracts the day, month and year from the TODAY function in cell C1, and then compiles them together again to form a date again using the DATE function.

# XL FORMULA FOCUS: SUM, AVERAGE, PRODUCT, POWER (an introduction to Excel math functions)

The picture above gives you a step by step encounter with 4 of Excel calculation functions. The numbers being used are in cells A1, B1 and C1. To make it easier to follow we’ve used the numbers 1, 2 and 3.

The blue cells contain the names of the functions we are using. Below each blue box is what you need to enter in the Formula Bar to make things happen. The next line shows the formula using the actual numbers instead of cell references. The line below that is the calculation that’s taking place, and last of all is the answer produced.

# XL FORMULA FOCUS: LEFT, RIGHT, MID, FIND (an introduction to Excel text functions)

These four functions are your essentials for manipulating text in Excel. Let’s take a closer look…

# LEFT

=left(text,num_chars)

As the name suggests, LEFT snips you a section of text starting from the left hand side. Our example in the picture above shows there are two parts; first you state which text you’re using, in this case the contents of cell B5; second you specify how many letters you want, in this case we chose 3 returning “XLC”.

# RIGHT

=right(text,num_chars)

RIGHT does exactly the same as LEFT but – yep you guessed it! – starting from the right hand side. So our example shows us taking the last 5 letters from cell B5 giving “Libre”.

# MID

=mid(text,start_num,num_chars)

MID takes things up a notch. It’s going to give us a number of characters from the middle of a text string, but we also need to tell it where to start. So the example above looks 3 characters along in B5, then gives you the next 3 characters “Cal”. If you changed it to =MID(B5,3,4) it would return “Cali”, whereas making it =MID(B5,4,3) would give “ali”.

# FIND

=FIND(find_text,within_text,start_num)

FIND checks for one text string within another one (e.g. here we look for “Libre” within “XLCalibre”) and tells us how many characters along it appears (in this case 5). You can tell it how many characters in to start (start_num), but don’t worry about that for now, you can leave it out if you want. There are two good uses for FIND that spring to mind:

1. Is the text there? Say you want to check whether someone has used a company e-mail address or not. When the e-mail address is in cell A1, you could try this:

=FIND(“@xlcalibre.com”,A1)

If our friend Al is using his XLCalibre e-mail address, al@xlcalibre.com, this formula will return 3, as the text string “@xlcalibre.com” starts from the third character. If it can’t find the string, it will return a #VALUE! error.

2. Combine FIND with other functions to manipulate text.

So we have our list of e-mail addresses starting in A1, and we know they will all be in the format firstname@xlcalibre.com. We want to get all of the first names. We could nest the FIND in a LEFT function like this:

=LEFT(A1,FIND(“@”,A1)-1)

For our example of “al@xlcalibre.com” this will return “al” Can you see how it works? If you omit the -1 at the end it gives “al@”.

You could go further and nest this in a PROPER function to get the capitalisation right:

=PROPER(LEFT(A1,FIND(“@”,A1)-1))

This returns “Al”.

For more info on PROPER and more text manipulation, check out our post on PROPER, UPPER, LOWER and LEN.

# XL FORMULA FOCUS: PROPER, UPPER, LOWER, LEN (your first four text functions)

The above picture gives a very quick lesson in four different functions. Here’s what they do:

# PROPER

Remember proper nouns from English class? Names of things, you know, stuff that deserves to start with a capital letter. This gives those words what they deserve. It’s particularly useful if you have a long list of names or address that have been entered in different ways, with all capitals, no capitals, or just plain shoddy capitalisation. This makes the first letter of each word a capital, and the rest lower case (of course it doesn’t work on words like XLCalibre (or McDonalds) where you need more than one capital).

# UPPER

This makes all letters in the specified text upper case. It’s that simple.

# LOWER

Guess what this does – it makes it all lower case.

# LEN

Just to throw you off guard, this tells you how many characters the text contains (LEN stands for length). XLCalibre is 9 characters long. Why is this useful? Well, the truth is on its not always that useful, but when combined with other functions it can be mighty handy. Trust me, if you’re going to learn more Excel functions you will want this in your repertoire.

So there you have it, an introduction to four text functions within the space of 5 minutes.

# XL FORMULA FOCUS: VLOOKUP for currency conversion

VLOOKUP is an essential Excel tool. Even if it’s the first function you learn, don’t worry, it’s actually pretty straight forward, and is really useful.

The purpose of the VLOOKUP function is to find your chosen value in a table of data and tell you what the corresponding value is in another part of that table. If that doesn’t make sense, follow this example and you’ll see what it means and why many Excel users consider it one of the most useful functions in their arsenal.

Currency conversion is essential when calculating international reward programs, like bonuses or salary review. You’ll usually need to work from a budget in a single currency to show the overall cost. So we’re going to use currency conversion to demonstrate the VLOOKUP function.

It’ll be easier for you to follow this if you have your own spreadsheet open to work through it with me. I’ve copied and pasted the latest exchange rates for pounds sterling (my local currency here in England) from Reuters’ website in columns A and B (but you’ll probably want to be consistent with the rates your finance department is using).

In the BLUE cell, D1, you enter your currency, in the format shown in the table. You may want to use data validation to put a dropdown for this, but how to do that will be the subject of another post. For our example, enter “USD” in this cell (don’t enter the quotation marks).

In the ORANGE, D3, cell enter an amount, let’s say 100. This is the number of United States Dollars (USD) that you will convert into pounds sterling.

In the GREEN cell, D5, we’re going to do our VLOOKUP. You start by typing in “=vlookup(” like I have here:

The grey box will appear below showing how this function works. You’ll notice the first thing it says is lookup_value. In this case we’re going to look for “USD” and return the conversion rate next to it. We’ve already entered this in cell D1, so we’ll just point our VLOOKUP to that, like so:

=vlookup(D1,

The comma after D1 is to show Excel that we’ve moved on to the next part of the function, which they described as table_array in their helpful grey box. This is our table of currencies and conversion rates, held in cells A1:B5. Note that you always need to make the first column of this array the place where your lookup values are. In this case, we’re looking for “USD”, and we start the range in column A. So we add this to our formula thus:

=vlookup(D1,A1:B5,

Again, a comma moves us on to the next part, col_index_number. We need to tell Excel how many columns across our table we’re going to look. There are two columns in our table, and we’re looking for the value (the currency conversion rate) in the second column, so we enter the number 2:

=vlookup(D1,A1:B5,2,

Again, remember the comma. This is the last part now. VLOOKUP gives you the option of whether you want to find an exact match for your lookup_value (in our case “USD”) or not. You will almost exclusively want to find an exact match (approximate matches might not always be what you would expect), so you need to enter FALSE here. Try not to worry about this part too much. For the time being always FALSE. As you develop your skills you may find a problem that requires TRUE, but this doesn’t happen so much in common uses an HR practitioner might have. So we end up with this:

=vlookup(D1,A1:B5,2,FALSE)

Notice that I’ve closed the brackets because this is the last part of the formula. I’ve also pressed enter, and you should too. So, what result does this formula give you? If you’ve followed everything so far, the answer should be 1.5875, the exchange rate for USD shown in our table. All you need to do now to make our example useful is multiply this by the value we entered in the orange cell, D3.

So select the green cell again, D5, and click at the end of your formula in the formula bar at the top of the screen. All we’re going to do is add “D3/” to the beginning of our formula, immediately after the =, meaning that we divide D3 by the result of our VLOOKUP. So your formula will now look like this:

=D3/VLOOKUP(D1,A1:B5,2,FALSE)

The answer should now say 62.99 (and probably a fair few more decimal places), which is how many pounds you would get for \$100. You can format this number to get rid of the extraneous decimal places – the shortcut for the formatting dialogue box is CTRL+1.

There is a copy of the finished spreadsheet here.

# XL FORMULA FOCUS: Find end of probation period using EDATE

So you’ve got a new employee, and you want to know when their probation period is over? The EDATE function is really straight forward.

=EDATE(Start_date,Months)

So type in “=EDATE(“, click on the cell that contains their start date, type a comma, then enter the number of months. 3 months? 6 months? Type a 3 or a 6 or whatever it is. Then close your brackets “)” and press enter.

Done.

RELATED: If you need to calculate length of service, you may want to look here.

# Splitting names

Today on LinkedIn somebody posed the following question (for anonymity I have changed any names given):

Sorting by Last Name

I have a list of 2,000 names by First, Middle, Last, Suffix (Jr or I,II, or III) I need to be able to Sort by last name. I know how to use “Text to Column” but unfortunately there is not a consistancy in the names, I have some that have just First and Last name, I have some that may have a space in their last name such as “De Souza” or “John Dave Jones Jr II” any suggestions.

The issue wasn’t really about sorting, which would be easy with autofilters (CTRL+Shift+L to apply autofilters). The problem was separating out the parts of the names, and lining them up in the right columns. Usually text to columns would fix this, but doing it with the different formats mentioned in the post would have left last names in various different columns.

I find this is a common predicament in HR, where lists of names pulled from various sources are full of inconsistencies, and therefore hard to manipulate or ‘plug in’ to other systems or spreadsheets.

My solution was not particularly elegant. You are rarely afforded the time to create the perfect single formula answer that covers all angles. But it does show you some text-manipulation functions at work.

I e-mailed the file to the poster. They mentioned 5 exceptions that it had not worked for, and they all included initials (I hadn’t thought of this possibility). This leads to my next point: testing. If at all possible you should use your spreadsheets from beginning to end with real data to check if they’re working before they go to your customer. Even better, get someone else to do this for you, as its easy to miss something when you’re buried in details.

Here you can access the spreadsheet that I created. See if you can understand how it works. How would you do it differently? Can you adjust it to work for with initials? Feel free to send your spreadsheets in to admin@xlcalibre.com.

If you’d be interested in similar discussions, come join the brand new XLCalibre Group on LinkedIn.