But first, let's do a quick brush up on basic functions. Consider the SUM function. By typing "=SUM()" into the formula bar and inserting a range of cells within the parentheses, for example "=SUM(A2:A5)," one cell can represent the sum of the values in cells A2, A3, A4 and A5. Try it out. Once you've wrapped your mind around how functions work, you'll be equipped to add these five to your repertoire.
TODAY is particularly useful for those in human resources or a management role, who may have to see how long an employee has worked at your company before being eligible for benefits such as vested stock or a 401K contribution. By referring to the date the employee was hired, TODAY calculates the days or years employed. You can also use TODAY to see the number of days between two dates. The best part? Every time you open up the spreadsheet, it will update the results of the formula based on the given day.
This function is great for many departments, especially those such as accounting where you need to calculate a value based on something being true or false. You can use IF to track dollar amounts within a particular timeframe and trust the numbers to tell you if a sales rep meets a specific goal for earning a bonus (aka, the situation is "true"). The same sort of logic can be applied to other conditions being met, such as meeting a deadline.
VLOOKUP—meaning vertical lookup—takes a value and compares it against a range of values, finds the highest-matching value within a data set (i.e. a table) and returns a corresponding value. Continuing with the commission example, VLOOKUP could search within your commission structure to find the corresponding annual bonus that should be paid to each salesperson. This function is particularly useful if you have a master spreadsheet with certain important employee information, but another person has been tracking birthdays in another sheet. You can use VLOOKUP to match employee names and pull birthdays into your master list.
As its name implies, SUMPRODUCT adds and multiplies at the same time. Let's say you have a spreadsheet showing different models of cars, the number of cars sold in a particular quarter and the price of each model. Normally, you would multiply the number of cars sold by the price of the relevant models, and then add the results for each model to get your total profit. SUMPRODUCT turns these two steps into one. A small benefit? Perhaps. But for many in the business world, every little hack counts.
Like a chain of holiday lights, sometimes in Excel when one cell goes out, they all go out. You've probably experienced the extreme frustration of setting up an entire spreadsheet and being unable to complete a calculation because one cell has an error value. This function helps you avoid such frustrations by applying different aggregate functions to a database with the option to ignore hidden rows or error values. Say you're looking at a spreadsheet concerning your employees and two of them are on maternity leave or sabbatical. Because you're not currently counting their sales numbers among your quarterly results, you may hide their rows. AGGREGATE, on the other hand can help you keep your calculations in tact while ignoring these employees' respective rows.