10 Excel Formula that Every Business Needs
Every professional in the accounts office or any financial position spends more time in excel than any other tool. There are some important formulas that one must pass when it comes to Microsoft excel general test to even qualify for such an office. Check out these top excel formulas below;
1. Index Match
This is one of the most advanced Microsoft excel formulas every professional must learn. It is an alternative to other formulas such as VLOOKUP and HLOOKUP. The two mentioned examples have got several limitations and drawbacks, hence the advanced options like Index Match. This is a massive merge of excel formulas that will make your financial analysis much easier.
Index, in Index Match, returns the cell value in a table and is based on the column and row numbers. Match, on the other side, returns the cell position in either a row or a column.
This is another great function for analysis in financial modeling. It lets you pick between two specific number options and return the choice you select. Here is an example;
You have three assumptions for revenue of growth, say, 6%, 13%, and 20%. If you use the Choose formula, you can select and choose number 2 and return 13%.
This is not exactly a function by itself. However, it is a clever way to join information and make worksheets better. For financial analysts doing financial modeling, Concatenate is a powerful tool that allows you to create great headers and labels.
This returns the number of cells, which meet in one condition. It can easily be applied in texts, dates, and numbers. They also support other logical operations and wildcards for partial matching. They come in three parts;
- Range, which refers to the range of cells you are looking to apply.
- Criteria, which refers to the criteria that is used to determine the cells to be added.
- Sum_range is also an options part of the entire formula. These are the cells you are looking to add together.
This function in Excel is used to count the cells in the range, which meet a single condition. They can be used in texts, dates, as well as numbers, and more. They also support logical operators and wildcards. The main function of COUNTIF is to match cells that match criteria.
This is used to count the average of numbers in a range, which meets the supplied criteria. They may also include logical operators and wildcards for partial matching. The number representing the average is the return value. Here are the three parts involved;
- Range, which refers to one or more cells; numbers, names, arrays, references, etc.
- Criteria, which refers to the expression, texts, numbers, or cell references.
- Average_range is an optional function
7. IF with And/ OR
Nested IF functions could be such a nightmare and this can be attested by anyone who has spent a large amount of time doing different tasks in financial models. Merging the IF and AND/ OR functions is a clever way of keeping formulas simple to use.
8. OFFSET with SUM/ AVERAGE
If you are well conversant with OFFSET you already know it is not an advanced function on its own. However, when it is combined with other functions such as SUM and AVERAGE, it becomes dynamic. The same goes with the regular AVERAGE and SUM formula. If used independently, they are limited and restricted but make pretty sophisticated functions when combined with other formulas.
9. XNPV and XIRR
These formulas can be applied in multiple functionalities including financial planning and analysis, investments banking, equity research, and any other position in corporate finance. They allow you to put specific numbers and dates to every individual cash flow being discounted.
The only downside to using these basic formulas is that they both assume time periods between the cash flows and the same, while as analysts not every cash flow is even, every time.
10. PMT and IPMT
These are best applicable in commercial banking, real estate, and FP&A, among others that deal with debt schedules. PMT will give you the value of the same payments over a period of loan given. In conjunction with IPMT, it will also tell you the interest payments for the same loan.