10 Advanced Excel Formulas You Must Know....................

Nowadays people work on MS EXCEL  so that they can do their work with ease and save their time too. There are many functions and formulas in MS Excel that make work easy and they help you in storing large amounts of data in a specified place. Anyone can work on MS Excel from anywhere and from any device. MS Excel has now become a part of everyone’s life be it a businessman or an employee or a simple school student. For using MS Excel in the best possible way it is important for you to know its advanced formulas as well as their use.

Following are 10 advanced formulas of MS Excel which will make your work easy. Let’s start  

Is Blank

This formula of MS Excel helps you in finding out whether the cell is blank or not for either alphabetical values or numerical values. It will write false if the cell is not empty and true if the cell is empty. The syntax for this command is as follows:

=ISBLANK(cell address)

PMT and IPMT

The PMT formula tells the equal amount of payment over the life of a loan whereas IPMT will tell you the interest payments for the same type of loan. They are very useful and advanced formulae for people who are working in real estate and commercial banking and also for those dealing with debts. The syntax of the two functions is as follows:

PMT: =PMT( interest_rate, number_payments, PV, [FV], [Type] )

IPMT: =IPMT(rate, per, nper, pv, [fv], [type])

IS ERROR

This is another advanced formula of MS Excel which is very useful and time-saving. It helps in finding out whether a specific cell has an error or not. By using this formula, you can find out the error in a specific cell and you can rectify it within no time. It will show true if there is any error else it will show false. The syntax of this formula is as follows:

=ISERROR(value)

XNPV and XIRR

XNPV formula is one of the most useful advanced formulas for those who work in the financial sector, as it helps in applying specific dates to each cash flow that is getting discounted. Rest of the formulas assumes the date but this does not do so; instead, it gives specific dates to each cash flow. The syntax for this formula is:

=XNPV(Discount Rate, Cash Flows, Dates of Cash Flow)

The XIRR function too is a financial function and is equally important. It will help you in finding the internal rate of return for cash flows that may or may not be periodic. The syntax of the XIRR function is as follows:

=XIRR(values, dates, [guess])

IS LOGICAL

This function basically tells whether the value of a specific cell is logical or not. It checks the value that is derived from the formula. When you will use this formula then it will show true if the value is logical, else it will show false. Suppose there is a blank cell or any random value is written; this formula then it will show the result as false. The syntax of this function is as follows:

=ISLOGICAL(cell address)

Choose

This is another advanced formula of MS Excel that can save time. It actually helps you in choosing from a lot of options and then returning the choice back. For example, if there are three assumptions for revenue growth that is 15%,25%, 35% and you want to choose 25%, then using this formula can select it and it will return the choice that is selected. The syntax of this function is as follows:

=CHOOSE (index_num, value1, [value2], …)

ISNONTEXT

This formula checks whether a value is text or not (blank cells are not text, by the way). If the value is text, then it will show true, else false. The syntax of this function is as follows:

=ISNONTEXT(value)

Index match

One of the advanced formulas of MS Excel is index match. It helps in searching or finding values and can be used on the rows and columns simultaneously, too. Suppose, for example, there is data in which different heights are written for different people and you want to find the height of a particular person then you can use this formula. The syntax for this one is as follows:

=INDEX(range, MATCH(lookup_value, lookup_range, match_type))

ISNUMBER

Is Number is one of the most useful advanced formulas of MS Excel. It actually shows you whether the contents of the cell are a number or not. If it is a number then it will show true else false. Blank cells will also be considered as a non-number. The syntax for this one is as follows:

=ISNUMBER(cell address)

LEN and TRIM

It is one of the advanced formulas of MS Excel and it is used especially in financial analytics. See what happens is that, financial analytics is used to organize as well as manipulate large amounts of data and MS Excel aptly helps in the same through these formulas. Sometimes while organizing the data, extra spaces are left at the beginning or end of the cell, so the TRIM function is used in order to remove those spaces. The TRIM function removes all the extra spaces. The syntax for the TRIM function is as follows:

=TRIM(text)

The LEN function, as its name suggests, is used for finding out the length of a string. The syntax for the same is as follows:

=LEN(text)

MS Excel is very useful in a lot of ways; it helps you in storing data and doing complex calculations using formulas as well. You can learn the use of these formulas and make your work much easier. Good Luck all