Tutorial on Advanced Excel Formulas

My earliest experience with MS Excel dates back to my college days when I and my friends were enlisting players for the next day’s match with another college. Be it tracking run rates, overs and total runs scored, we frantically filled the spreadsheet with as much relevant data as possible just to make sense out of it. It can rather be termed as perhaps my first experience into data analysis, graphs and pie-charts. I am still thankful to Excel since we could plan our approach to the game systematically.

Microsoft Excel is one of the most simplest and powerful software applications available out there. It lets users do quantitative analysis, statistical analysis with an intuitive interface for data manipulation, so much so that its usage spans across different domains and professional requirements.

Rather than focusing on what excel is and how to do simple subtraction, addition, we shall look into some of the advanced formulas which are frequently asked in interviews when it comes to analyzing a candidate’s Excel skills. Advanced Excel is quite different from Basic Excel,  the focus for the user is more on DSUM, DCOUNT, Pivot Table, Pivot Chart, Formulas, Functions, and Macros.

Some of the other important concepts to explore while working on Advanced Excel are:

• If Statements
• Sum Products
• VLOOKUP
• Concatenate
• Sum Ifs

Let’s take a Scenario where Ted, a Mobile Reseller has a data that sold different products last month.

IF Statements

The ‘IF’ statement is a fundamental concept that helps to look up how a given data reacts differently at various instances. To put it in simpler terms, suppose Ted wants to know the best-selling product last month where according to him any phone that has sold more than 250 units is high in demand and anything below it is low in demand.

The Syntax for the Statement would be:

=IF(D2>250, “High in Demand”, “Not High in Demand”)

This will then fetch the results:

From the above table we can understand that Sony Xperia & HTC Desire is a high performing product (much better than Android : I must admit I was a little biased towards Android products while creating this data)

Sum Products

The Sum-Product Formula is important when it comes to excel calculations. The feature of this formula is that it takes one or more array of numbers and gets the sum of products of corresponding numbers.

Suppose Ted wants to know how much income he has generated last month where he has sold different smart phones at different price ranges.

The Syntax is as follows:

=SUMPRODUCT (C2:C6, D2:D6)

C2 and C6 refer to the Price and D2/D6 refers to the number of units sold; the column names change as per the data.

This will fetch a total revenue result of $5,44,800.

Concatenate

It’s a big word yes, but not to worry. Concatenate is a formula for combining data in 2 or more different cells into one cell. Suppose we have the list of products sold and the verdict of each product, Ted then wishes to associate each product with its verdict, so here we use the Syntax:

=Concatenate(A1, “&”, B1)

This also depends on the cell you want to merge as well.

It will hence give the following result

VLOOKUP

The VLOOKUP function is used to search the first column of a range of cells, and then return a value from any cell on the same row of range.

In this case suppose Ted has the Product ID of each product and needs to find out what product it is ( assuming he has thousands of items)

He needs to find out the name of the product with the product ID ‘56’.

Then he uses the following syntax:

=VLOOKUP(56, A2:B6, 2, FALSE)

Here, the formula looks for the product ID ‘56’ in the range of A2 to B6 and fetches the value from Column 2.

Excel is as simple as that. It’s all about understanding the concept, using the syntax and fetching the right result for analysis.

Got a question for us? Mention them in the comments section and we will get back to you.

Related Posts:

Advanced Excel Training