Sep
10
2018

Excel Advanced Features and Functions

Name Product: Excel Advanced Features and Functions
Download Size: 2.7 GB
COST: $44= Yours Free
Sale Page: _https://corporatefinanceinstitute.com/resources/excel/study/advanced-excel-formulas-must-know/

1. INDEX MATCH

Formula: =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))

This is an advanced alternative to the VLOOKUP or HLOOKUP formulas (which several drawbacks and limitations).  INDEX MATCH is a powerful combination of Excel formulas that will take your financial analysis and financial modeling to the next level.

INDEX returns the value of a cell in a table based on the column and row number.

MATCH returns the position of a cell in a row or column.

2. IF combined with AND / OR

Formula: =IF(AND(C2>=C4,C2<=C5),C6,C7)

Anyone who’s spent a great deal of time in various types of financial models knows that nested IF formulas can be a nightmare.  Combining IF with the AND or the OR function can be a great way to keep or formulas easier to audit and for other users to understand.  In the example below, you will see how we used the individual functions in combination to create a more advanced formula.

3. OFFSET combined with SUM or AVERAGE

Formula: =SUM(B4:OFFSET(B4,0,E2-1))

The OFFSET function on its own is not particularly advanced, but when we combine it with other functions like SUM or AVERAGE we can create a pretty sophisticated formula.   Suppose you want to create a dynamic function that can sum a variable number of cells.  With the regular SUM formula, you are limited to a static calculation, but by adding OFFSET you can have the cell reference move around.

How it works.  To make this formula work we substitute ending reference cell of the SUM function with the OFFSET function.  This makes the formula dynamic and cell referenced as E2 is where you can tell Excel how many consecutive cells you want to add up. Now we’ve got some advanced Excel formulas!

4. CHOOSE

Formula: =CHOOSE(choice, option1, option2, option3)

The CHOOSE function is great for scenario analysis in financial modeling.  It allows you to pick between a specific number of options, and return the “choice” that you’ve selected.  For example, imagine you have three different assumptions for revenue growth next year: 5%, 12% and 18%.  Using the CHOOSE formula you can return 12% if you tell Excel you want choice #2.


Do you want download faster without waiting time ? We Strong Recommend You Purchase an Ausfile Premium Account ! It's very cheap only 9.99$/month. Follow the link below: Ausfile.com
By this way, You will help us very much to maintain our server and keep Biz-tutorial.com to alive

Related Posts

About the Author:

Biz-Tutorial.com started as a discussion platform with the simple purpose allowing members to download any business tutorials or whatever they want !

Leave a comment

You must be logged into post a comment.

COMPANY NAME
221, Mount Olimpus, Rheasilvia, Mars,
Solar System, Milky Way Galaxy
+1 (999) 123-45-67
Thank You. We will contact you as soon as possible.
Ivan Churakov, developer
Tel.:
Fax:
E-mail:
Website:
+1 (800) 800-1234, +1 (800) 123-4567
+1 (800) 800-1234 (ext. 1234)
[email protected]
http://halfdata.com/
My CodeCanyon Portfolio
Banner Manager Pro - CodeCanyon Item for Sale
Coming Soon and Maintenance Mode - CodeCanyon Item for Sale
Code Shop - CodeCanyon Item for Sale
Keyword Tooltips - CodeCanyon Item for Sale
Subscribe & Download - CodeCanyon Item for Sale
PGlmcmFtZSB3aWR0aD0iMTAwJSIgaGVpZ2h0PSIxMDAlIiBzcmM9Ii8vd3d3LnlvdXR1YmUuY29tL2VtYmVkL3NCV1BDdmR2OEJrP2F1dG9wbGF5PTEiIGZyYW1lYm9yZGVyPSIwIiBhbGxvd2Z1bGxzY3JlZW4+PC9pZnJhbWU+
ENJOY AURORA BOREALIS
INTERGALACTIC COMPANY
"Ridiculus enim cras placerat facilisis amet lorem ipsum scelerisque sagittis lorem tis!"
Jojn Doe, CEO
Tel.: +1 (800) 123-45-67, +1 (800) 123-45-68
Fax: +1 (800) 123-45-69 (any time, 24/7/365)
E-mail: [email protected]
Website: http://www.intergalactic.company
Address:
221, Mount Olimpus,
Rheasilvia region, Mars,
Solar System, Milky Way Galaxy