If there’s one thing you need to know to get the most out of Excel, it’s formulas. These are commands that tell the software to do a huge range of different things and can make significant time and efficiency savings once you master them.
Index Match
Index Match can be used as an alternative to the VLOOKUP or HLOOKUP formulas and cuts out the limitations that these naturally have. It is a powerful combination formula that is ideal for financial analysis and modelling. In it, there are two components:
• INDEX – this returns the value of the cell based on column and row number
• MATCH – this returns the position of a cell in a row or column
For example, if you had some data on a person with their name, height and weight in columns C, D and E, you could use the formula to find out the height of a person by searching for their name. An example of the formula would look like
+INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))
IF with AND / OR
Using a number of IF formulas can quickly turn into a headache for financial calculations. The key to making them less stressful and easier to audit is to use the AND / OR functions alongside the IF. With this formula, you will get a value based on two different numbers. An example of the AND formula would be
=IF(AND(C6>=C8,C6<=C9),C11,C12)
OFFSET with SUM or AVERAGE
OFFSET on its own might not seem the most useful but it can be combined with other functions to widen its use. It can create an offset formula rather than a normal static one and you can also have the cell reference move around as needed. The formula in action might look like
=SUM(B4:OFFSET(B4,0,E2-1))
CHOOSE
The CHOOSE function is great for scenario analysis and lets you select from specific option and have the return show the choice you have selected. Say you have three different percentage growth figures – 10%, 15% and 20% in three rows of column D. By using this formula, you can choose one of them and have it show in the result. The formula could look like
=CHOOSE(choice, option1, option2, option3)
SUMIF and COUNTIF
These two formulas are used in similar ways. With SUMIF, the idea is that all cells that meet the specific criteria are added to the result. Or with the COUNTIF formula, it counts all cells that meet your criteria. Say you wanted to count all cells that had a figure greater than 20 in them. You could use COUNTIF to tell you how many there were. An example of the formula would be
=COUNTIF(D5:D12,”>=21″)
Complex formulas
These are a few examples of the more complex formulas you can learn to use in Excel. They allow you to get more done with minimal extra work by making use of the clever features built into Excel and combining them in different ways for more results.
Want to learn more about formulas in Excel? Enrol on to one of our Excel training courses and see how much you could formulate! You can call us on 01454 203355 or use our contact form and we will get back to you.