[sharify]

Excel, you’ve probably heard of it, even if you’ve never used it. Most jobs today, especially analysis, finance, statistics or any number driven function really, requires it. Today we’re going to discuss the four excel formulas and as a bonus the one Excel function that will help you land the job you’re gunning for.

1. VLOOKUP

Use: To quickly find information in a table, by looking up information within a column and retrieving other information from a row that fits the criteria.

Formula: =vlookup(lookup_value, table_array, col_index_num, [range_lookup])

A function so important to most bosses, that it’s mentioned even in job ads. Vlookup is used to find information about a row in a table.

Just in case, you didn’t believe me – a job posting from Jobscentral

So lets get started with an example.

Say you have a table, like the one below, and you want to use an excel formula to find out the “Freshness” of fruit, in this case we’ll use “Pears”.

The formula we would use would look like

“=VLOOKUP(“Pears”,A1:C6,3,0)“

So lets go through the formula part by part:

• Lookup_value – Basically the thing you’re looking for. In this case its “Pears”. Where we use text in the V lookup formula, we must use open and close inverted commas to indicate that this is text. If you’re searching for a number like 1, you would simply type 1 without any open/ closed inverted commas
• Table_array – The whole table that you’re going to search through, the lookup value must be the leftmost column of this table. In this case, our table started from A1 and ended at C6, thus giving us A1: C6.
• col_index_num – The column from which a result will be returned, relative to the first column in the table. In our case our table started from column A, which makes column A, 1, Column B would be 2, and column C would be 3.
• Range_lookup – Should always be 0. This would ensure that only results from a row which matched your lookup value EXACTLY would be returned.

2. Sum

Use: to add the value of a group of cells

Formula:=sum(range)

The sum function is pretty self-explanatory, its use is to add the value of a range of cells together. So say we wanted to find the total number of fruits we had in storage, we would use a formula like this.

“=sum(B2:B6)“

And we would get the answer 14,780

3. Count

Use: To count the number of cells where there are numbers

Formula: =COUNT(value)

A function used to count the number of cells where there are numbers. Useful where there are big tables, and some of the cell contents are blank while others have numbers.

The formula we would use would look like

“=COUNT(B2:B6)“

Just insert the cell range where you’d like to count if numbers are present in between the brackets

4. IF

Use: To tell you if a certain criteria has been met, and then return a true or false value
Formula: =if(logical_statement, result if true, result if false)

So the logical statement would be where you place a condition, based on this condition, the IF function returns a result if true, or false. This is useful when we’re trying to input logic onto a table. So lets say the fruit company wants to sell only fresh fruit, we would use a formula like this:

“=if(C3=”FRESH”,”Can Sell”,”Cannot Sell”)“

To help tell us which fruits, we can or cannot sell. So Oranges cannot be sold.

So lets go through the formula part by part:

• logical_statement – The condition that must be met, if it is met, the true result is returned. Otherwise a false result is returned. In this case, the condition was that C3 = “Fresh”
• True result – The result or formula to be displayed/ calculated when the logical statement is true. In this case, we used the result “Can Sell”
• False result – The result or formula to be displayed/ calculated when the logical statement is false. In this case, we used the result “Cannot Sell”

What else should I learn?

Pivot tables

Helpful when analysing large sets of data and condensing them into a more detailed form of analysis, knowing how to use pivot tables is a fairly marketable skill, with many employers looking for recruits who are proficient in this.

We’ve enjoyed sharing our take on the four excel formulas that will help you ace any interview. What we’ve covered here are the basics, but if you’re interested in learning more, which you should attend the  Excel Formulas Made Easy – Learn more than 100 Formulas by Udemy to get a better grasp of Excel. Think your friends might benefit from reading this article? Share it! Think there are other formulas or functions in excel everyone should know? Or perhaps you disagree with something we said? Share your view by leaving a comment below and we’ll discuss it.