ADVANCE EXCEL TRAINING IN CHANDIGARH
5 Advanced Excel Formulas You 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 formulas that will take your financial analysis and financial modeling to the next level.
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,E21)) The OFFSET function on its own in 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
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, image you have three different assumptions for revenue growth next year: 5%, 12% and 18%. Using the CHOOSE formula you return 12% if we tell Excel you want choice #2.
5. XNPV and XIRR Formula: =XNPV(discount rate, cash flows, dates) If you’re an analyst working in investment banking, equity research, or financial planning & analysis (FP&A), or any other area of corporate finance that requires discounting cash flows then these formulas are a lifesaver! Simply put, XNPV and XIRR allow you to apply specific dates to each individual cash flow that’s being discounted. The problem with Excel’s basic NPV and IRR formulas is that it assumes the time periods between cash flow are equal. Routinely, as an analyst you’ll have situations are cash flow are not timed evenly, and this formula is how you fix that.
SCO 23-24-25, Sector 34A Chandigarh, IN 160022 (+91) 9988741983 counselor.cbitss@gmail.com
WEBSITE :-
http://cbitss.co.in/advance-excel-training-in-chandigarh.html
THANK YOU