≡ Menu

Advanced Excel Formulas with Examples

In this tutorial I will teach you about some advance formulas in excel 2013.

The whole of excel 2013 stands on the pillar of formulas. This means that without formulas, excel is just a simple data keeping tool. Using formulas enable you to crunch numbers in order to analyze it and get answers to complex questions. There are simple formulas that anyone can use as a daily routine such as Sum, Average and IF. There are many specialized and advance level formulas that makes excel the best application for number crunching.

Advance excel users know how to debug and audit them in order to use the formula efficiently and effectively.

I will show you some of the examples of advance level formulas that includes

VLOOKUP.

HLOOKUP.

PMT.

Absolute References.

VLOOKUP

V in this function stands for Vertical. This function search the first value in a column of a selected table and returns a value in the same row from another column of the range.

Let me explain this with an example.

We will have inventory of some items and then from the product code to get the price.

From this data we will get the price of the item from the code

We will apply the formula of VLOOKUP as per the following diagram

We will get the total amount using VLOOKUP function to get the amount of a bill.

HLOOKUP

The HLOOKUP Function works the same as VLOOKUP but the only difference is that instead of columns, it works on the Rows to calculate the values.

PMT

PMT function is used to calculate the loan payment with the interest rates. This function has 3 compulsory arguments and 2 optional arguments.

RATE: the interest rate.

Nper: total number of payment for the loan.

PV: the present value which means the series of future payments worth as of today. This is also known as principle.

FV: Optional argument that means future value you want to achieve after the last payment. If no argument is given, it is assumed to be zero.

Type: Optional argument meaning that when the payment is due. 0 means that payment is due at the end of the period while 1 means at the beginning of the period. If no value is given, it is assumed 0.

By applying the formula we will get the per month value.

Notice that we have taken the interest rate by 12 months as we have to calculate per month payment.

Absolute References

Sometimes when we drag a formula to the row or column, by default excel automatically adjust the references and paste the formula to the other relative position. For example, moving the formula to right cell will move the reference of the formula to one column right. This reference is called relative references. In absolute reference, we can fix the row, column or range so there where ever we copy and paste the formula, the range remain the same. This is done by editing the formula and press the function key F4. This will give a $ sign before the row-column cell name.

{ 0 comments… add one }

Leave a Comment