×

Search our courses

6 Excel formulas you should know

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.

Excel Courses

Leave a Reply

Your email address will not be published. Required fields are marked *