Top 10 Advanced Excel formulas with examples in Excel sheet Free Download<\/strong><\/h2>\n1. IF combined with AND \/ OR<\/strong><\/h4>\nFormula: =IF(AND(C2>=C4,C2<=C5),C6,C7)<\/p>\n
Combining IF with the AND or the OR function can be a great way to keep formulas easier to audit and easier for other users to understand.<\/p>\n
![\"Advanced](\"https:\/\/cdn.corporatefinanceinstitute.com\/assets\/how-to-use-if-and-advanced-excel-formulas.jpg\")
<\/p>\n
2. CONCATENATE<\/strong><\/h4>\nFormula: =A1&\u201d more text\u201d<\/p>\n
This is a very powerful tool for financial analysts performing financial modeling.<\/p>\n
![\"Concatenate](\"https:\/\/cdn.corporatefinanceinstitute.com\/assets\/concatenate-in-excel.jpg\")
<\/p>\n
3. SUMIF and COUNTIF<\/strong><\/h4>\nFormula: =COUNTIF(D5:D12,\u201d>=21\u2033)<\/p>\n
These two advanced formulas are great uses of conditional functions.\u00a0 SUMIF adds all cells that meet certain criteria, and COUNTIF counts all cells that meet certain criteria.<\/p>\n
![\"COUNTIF](\"https:\/\/cdn.corporatefinanceinstitute.com\/assets\/countif-formula-in-action.jpg\")
<\/p>\n
Ready to take your data science skills to the next level? Sign up for a free demo today!<\/a><\/strong><\/p>\n\u00a04. PMT and IPMT<\/strong><\/h4>\nFormula: =PMT(interest rate, # of periods, present value)<\/p>\n
The PMT formula gives you the value of equal payments over the life of a loan.\u00a0 You can use it in conjunction with IPMT (which tells you the interest payments for the same type of loan), then separate principal and interest payments.<\/p>\n
![](\"https:\/\/cdn.corporatefinanceinstitute.com\/assets\/Untitled-300x183.jpg\")
<\/p>\n
5. XNPV and XIRR<\/strong><\/h4>\nFormula: =XNPV(discount rate, cash flows, dates)<\/p>\n
This formula allows you to apply specific dates to each individual cash flow that\u2019s being discounted.\u00a0 The problem with Excel\u2019s basic NPV and IRR formulas is that they assume the time periods between cash flow are equal. Sometimes, as an analyst, you\u2019ll have to face the situations where cash flows are not timed evenly, and this formula will help you to fix it.<\/p>\n
![\"XNPV](\"https:\/\/cdn.corporatefinanceinstitute.com\/assets\/XNPV-formula-2-1024x433.png\")
<\/p>\n
6. CHOOSE<\/strong><\/h4>\nFormula: =CHOOSE(choice, option1, option2, option3)<\/p>\n
This formula allows you to pick between a specific number of options, and return the \u201cchoice\u201d that you\u2019ve selected.<\/p>\n
![\"CHOOSE](\"https:\/\/cdn.corporatefinanceinstitute.com\/assets\/advanced-formulas-choose-option.jpg\")
<\/p>\n
7. OFFSET combined with SUM or AVERAGE<\/strong><\/h4>\nFormula: =SUM(B4:OFFSET(B4,0,E2-1))<\/p>\n
To run this formula, we substitute the ending reference cell of the SUM function with the OFFSET function.\u00a0 This makes the formula dynamic and the cell referenced as E2 is where you can tell Excel how many consecutive cells you want to add up.<\/p>\n
![\"sum](\"https:\/\/cdn.corporatefinanceinstitute.com\/assets\/sum-offset-formula.png\")
<\/p>\n
8. INDEX MATCH<\/strong><\/h4>\nFormula: =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))<\/p>\n
This is a powerful combination of Excel formulas that will take your financial analysis and financial modeling to the next level.<\/p>\n
INDEX returns the value of a cell in a table based on the column and row number.<\/p>\n
MATCH\u00a0 returns the position of a cell in a row or column.<\/p>\n
![\"Advanced](\"https:\/\/cdn.corporatefinanceinstitute.com\/assets\/example-of-index-match.jpg\")
<\/p>\n
<\/p>\n
Looking for a Data Science Career? Explore Here!<\/a><\/strong><\/p>\n9. LEN and TRIM<\/strong><\/h4>\nFormulas: =LEN(text) and =TRIM(text)<\/p>\n
These formulas are very useful for financial analysts who are dealing with large amount of data. Unfortunately, the data we get is not always perfectly organized and sometimes, there can be issues like extra spaces at the beginning or end of cells.<\/p>\n
The LEN formula returns a given text string as the number of characters, which is useful when you want to count how many characters there are in some text.<\/p>\n
The TRIM formula cleans up the extra spaces in the Excel data.<\/p>\n
![\"Advanced](\"https:\/\/cdn.corporatefinanceinstitute.com\/assets\/trim-formula-to-remove-spaces-in-excel.jpg\")
<\/p>\n
10. CELL, LEFT, MID and RIGHT\u00a0<\/strong><\/h4>\nThese excel functions can be combined to create advanced and complex formulas to use.<\/p>\n
The CELL<\/strong> function can return a variety of information about the contents of a cell (such as its name, location, row, column, and more).<\/p>\nThe LEFT<\/strong>\u00a0function can return text from the beginning of a cell (left to right).<\/p>\nThe MID<\/strong> returns text from any start point of the cell (left to right).<\/p>\nThe RIGHT<\/strong> \u00a0returns text from the end of the cell (right to left).<\/p>\n![\"CELL,](\"https:\/\/cdn.corporatefinanceinstitute.com\/assets\/examples-of-left-mid-right-excel.jpg\")
<\/p>\n
Some Basic Excel Formulas\u00a0<\/strong><\/h4>\n\n\n- SUM: Calculates the sum of a range of cells. Example: =SUM(A1:A10)<\/strong><\/li>\n
- AVERAGE: Calculates the average of a range of cells. Example: =AVERAGE(A1:A10)<\/strong><\/li>\n
- MAX: Returns the maximum value in a range of cells. Example: =MAX(A1:A10)<\/strong><\/li>\n
- MIN: Returns the minimum value in a range of cells. Example: =MIN(A1:A10)<\/strong><\/li>\n
- COUNT: Counts the number of cells in a range that contain a value. Example: =COUNT(A1:A10)<\/strong><\/li>\n
- IF: Returns one value if a condition is true and another value if it is false. Example: =IF(A1>10, “Greater than 10”, “Less than or equal to 10”)<\/strong><\/li>\n
- VLOOKUP: Searches for a value in the first column of a table and returns a corresponding value in the same row from another column. Example: =VLOOKUP(A1, B1:C10, 2, FALSE)<\/strong><\/li>\n
- CONCATENATE: Combines two or more text strings into one. Example: =CONCATENATE(“John”, ” “, “Doe”)<\/strong><\/li>\n
- LEFT: Returns a specified number of characters from the beginning of a text string. Example: =LEFT(A1, 5)<\/strong><\/li>\n
- RIGHT: Returns a specified number of characters from the end of a text string. Example: =RIGHT(A1, 5)<\/strong><\/li>\n<\/ol>\n<\/div>\n
Formula: =IF(AND(C2>=C4,C2<=C5),C6,C7)<\/p>\n
Combining IF with the AND or the OR function can be a great way to keep formulas easier to audit and easier for other users to understand.<\/p>\n
<\/p>\n
2. CONCATENATE<\/strong><\/h4>\nFormula: =A1&\u201d more text\u201d<\/p>\n
This is a very powerful tool for financial analysts performing financial modeling.<\/p>\n
<\/p>\n
3. SUMIF and COUNTIF<\/strong><\/h4>\nFormula: =COUNTIF(D5:D12,\u201d>=21\u2033)<\/p>\n
These two advanced formulas are great uses of conditional functions.\u00a0 SUMIF adds all cells that meet certain criteria, and COUNTIF counts all cells that meet certain criteria.<\/p>\n
<\/p>\n
Ready to take your data science skills to the next level? Sign up for a free demo today!<\/a><\/strong><\/p>\n\u00a04. PMT and IPMT<\/strong><\/h4>\nFormula: =PMT(interest rate, # of periods, present value)<\/p>\n
The PMT formula gives you the value of equal payments over the life of a loan.\u00a0 You can use it in conjunction with IPMT (which tells you the interest payments for the same type of loan), then separate principal and interest payments.<\/p>\n
<\/p>\n
5. XNPV and XIRR<\/strong><\/h4>\nFormula: =XNPV(discount rate, cash flows, dates)<\/p>\n
This formula allows you to apply specific dates to each individual cash flow that\u2019s being discounted.\u00a0 The problem with Excel\u2019s basic NPV and IRR formulas is that they assume the time periods between cash flow are equal. Sometimes, as an analyst, you\u2019ll have to face the situations where cash flows are not timed evenly, and this formula will help you to fix it.<\/p>\n
<\/p>\n
6. CHOOSE<\/strong><\/h4>\nFormula: =CHOOSE(choice, option1, option2, option3)<\/p>\n
This formula allows you to pick between a specific number of options, and return the \u201cchoice\u201d that you\u2019ve selected.<\/p>\n
<\/p>\n
7. OFFSET combined with SUM or AVERAGE<\/strong><\/h4>\nFormula: =SUM(B4:OFFSET(B4,0,E2-1))<\/p>\n
To run this formula, we substitute the ending reference cell of the SUM function with the OFFSET function.\u00a0 This makes the formula dynamic and the cell referenced as E2 is where you can tell Excel how many consecutive cells you want to add up.<\/p>\n
<\/p>\n
8. INDEX MATCH<\/strong><\/h4>\nFormula: =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))<\/p>\n
This is a powerful combination of Excel formulas that will take your financial analysis and financial modeling to the next level.<\/p>\n
INDEX returns the value of a cell in a table based on the column and row number.<\/p>\n
MATCH\u00a0 returns the position of a cell in a row or column.<\/p>\n
<\/p>\n
<\/p>\n
Looking for a Data Science Career? Explore Here!<\/a><\/strong><\/p>\n9. LEN and TRIM<\/strong><\/h4>\nFormulas: =LEN(text) and =TRIM(text)<\/p>\n
These formulas are very useful for financial analysts who are dealing with large amount of data. Unfortunately, the data we get is not always perfectly organized and sometimes, there can be issues like extra spaces at the beginning or end of cells.<\/p>\n
The LEN formula returns a given text string as the number of characters, which is useful when you want to count how many characters there are in some text.<\/p>\n
The TRIM formula cleans up the extra spaces in the Excel data.<\/p>\n
<\/p>\n
10. CELL, LEFT, MID and RIGHT\u00a0<\/strong><\/h4>\nThese excel functions can be combined to create advanced and complex formulas to use.<\/p>\n
The CELL<\/strong> function can return a variety of information about the contents of a cell (such as its name, location, row, column, and more).<\/p>\nThe LEFT<\/strong>\u00a0function can return text from the beginning of a cell (left to right).<\/p>\nThe MID<\/strong> returns text from any start point of the cell (left to right).<\/p>\nThe RIGHT<\/strong> \u00a0returns text from the end of the cell (right to left).<\/p>\n<\/p>\n
Some Basic Excel Formulas\u00a0<\/strong><\/h4>\n\n\n- SUM: Calculates the sum of a range of cells. Example: =SUM(A1:A10)<\/strong><\/li>\n
- AVERAGE: Calculates the average of a range of cells. Example: =AVERAGE(A1:A10)<\/strong><\/li>\n
- MAX: Returns the maximum value in a range of cells. Example: =MAX(A1:A10)<\/strong><\/li>\n
- MIN: Returns the minimum value in a range of cells. Example: =MIN(A1:A10)<\/strong><\/li>\n
- COUNT: Counts the number of cells in a range that contain a value. Example: =COUNT(A1:A10)<\/strong><\/li>\n
- IF: Returns one value if a condition is true and another value if it is false. Example: =IF(A1>10, “Greater than 10”, “Less than or equal to 10”)<\/strong><\/li>\n
- VLOOKUP: Searches for a value in the first column of a table and returns a corresponding value in the same row from another column. Example: =VLOOKUP(A1, B1:C10, 2, FALSE)<\/strong><\/li>\n
- CONCATENATE: Combines two or more text strings into one. Example: =CONCATENATE(“John”, ” “, “Doe”)<\/strong><\/li>\n
- LEFT: Returns a specified number of characters from the beginning of a text string. Example: =LEFT(A1, 5)<\/strong><\/li>\n
- RIGHT: Returns a specified number of characters from the end of a text string. Example: =RIGHT(A1, 5)<\/strong><\/li>\n<\/ol>\n<\/div>\n
<\/p>\nFormula: =COUNTIF(D5:D12,\u201d>=21\u2033)<\/p>\n
These two advanced formulas are great uses of conditional functions.\u00a0 SUMIF adds all cells that meet certain criteria, and COUNTIF counts all cells that meet certain criteria.<\/p>\n
<\/p>\n
Ready to take your data science skills to the next level? Sign up for a free demo today!<\/a><\/strong><\/p>\n Formula: =PMT(interest rate, # of periods, present value)<\/p>\n The PMT formula gives you the value of equal payments over the life of a loan.\u00a0 You can use it in conjunction with IPMT (which tells you the interest payments for the same type of loan), then separate principal and interest payments.<\/p>\n Formula: =XNPV(discount rate, cash flows, dates)<\/p>\n This formula allows you to apply specific dates to each individual cash flow that\u2019s being discounted.\u00a0 The problem with Excel\u2019s basic NPV and IRR formulas is that they assume the time periods between cash flow are equal. Sometimes, as an analyst, you\u2019ll have to face the situations where cash flows are not timed evenly, and this formula will help you to fix it.<\/p>\n Formula: =CHOOSE(choice, option1, option2, option3)<\/p>\n This formula allows you to pick between a specific number of options, and return the \u201cchoice\u201d that you\u2019ve selected.<\/p>\n Formula: =SUM(B4:OFFSET(B4,0,E2-1))<\/p>\n To run this formula, we substitute the ending reference cell of the SUM function with the OFFSET function.\u00a0 This makes the formula dynamic and the cell referenced as E2 is where you can tell Excel how many consecutive cells you want to add up.<\/p>\n Formula: =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))<\/p>\n This is a powerful combination of Excel formulas that will take your financial analysis and financial modeling to the next level.<\/p>\n INDEX returns the value of a cell in a table based on the column and row number.<\/p>\n MATCH\u00a0 returns the position of a cell in a row or column.<\/p>\n <\/p>\n Looking for a Data Science Career? Explore Here!<\/a><\/strong><\/p>\n Formulas: =LEN(text) and =TRIM(text)<\/p>\n These formulas are very useful for financial analysts who are dealing with large amount of data. Unfortunately, the data we get is not always perfectly organized and sometimes, there can be issues like extra spaces at the beginning or end of cells.<\/p>\n The LEN formula returns a given text string as the number of characters, which is useful when you want to count how many characters there are in some text.<\/p>\n The TRIM formula cleans up the extra spaces in the Excel data.<\/p>\n These excel functions can be combined to create advanced and complex formulas to use.<\/p>\n The CELL<\/strong> function can return a variety of information about the contents of a cell (such as its name, location, row, column, and more).<\/p>\n The LEFT<\/strong>\u00a0function can return text from the beginning of a cell (left to right).<\/p>\n The MID<\/strong> returns text from any start point of the cell (left to right).<\/p>\n The RIGHT<\/strong> \u00a0returns text from the end of the cell (right to left).<\/p>\n\u00a04. PMT and IPMT<\/strong><\/h4>\n
<\/p>\n5. XNPV and XIRR<\/strong><\/h4>\n
<\/p>\n6. CHOOSE<\/strong><\/h4>\n
<\/p>\n7. OFFSET combined with SUM or AVERAGE<\/strong><\/h4>\n
<\/p>\n8. INDEX MATCH<\/strong><\/h4>\n
<\/p>\n9. LEN and TRIM<\/strong><\/h4>\n
<\/p>\n10. CELL, LEFT, MID and RIGHT\u00a0<\/strong><\/h4>\n
<\/p>\nSome Basic Excel Formulas\u00a0<\/strong><\/h4>\n
\n