Excel Advanced Features and Functions

One of the most used feature of Excel is the ability to lookup for certain values in a cell area dynamically.
To accomplish this, Excel provides us with some interesting functions. 
During the overview of these functions we will find out their abilities and limitations.
In doing so we will determine which is the best and most versatile lookup function.
This quest is going to lead us to the conclusion that when dealing with numbers, the power comes in ... numbers, and that the best lookup function is actually a combination of functions.
This is kind of a lengthy chapter but bear with us since the result will be rewarding.

Probably the easiest to understand lookup function is the choose function.

It returns a value from an array of values, that corresponds to a specific position in that array. Choose can have up to 254 value arguments. 

The index function is one of the most interesting functions in excel. It can be combined with many other functions to help you out of some difficult situations.

It comes in two formats. An array format and a range format. The array format looks up a reference to a cell within a single, cell range

There is no way to create a powerful and versatile lookup formula without using at least once the match function.

The match function looks up information from an array of data, just like the choose function we saw in a previous lesson.

The difference is that the match function returns the position of the lookup value in the array of data.

The match function also allows us to choose if it will return an exact match of the requested value, or the closest match (above or below) the requested value.

One more very useful Excel function is Offset.

It returns a range of cells which corresponds to a specified number of rows and columns starting at a given distance from a reference cell.

The lookup function has two forms. The Vector form and the array form.

Microsoft proposes not to use the array form and use their two other lookup functions. Vlookup and Hlookup.

But in truth, although it is a powerful function, lookup is hardly used at all.

We will begin a quest to find the best lookup function. All the internal functions of Excel have limitations which we will try to overcome.

In this and the following lessons, the knowledge of the simple syntax of the functions mentioned, is considered a requirement.

The basic limitation of VLOOKUP and HLOOKUP is that they are single dimensional. They only search vertically or horizontally. The most obvious way to overcome this is by combining the two of them in a single formula.

In short we will try to make VLOOKUP search in a matrix (both vertically and horizontally). To do this we will use a VLOOKUP function with a nested HLOOKUP to provide us with the column number attribute of the VLOOKUP.

We will continue our quest for the best lookup formula with another combination. We will use an offset and two match functions to do a matrix lookup. Unfortunately, this formula also only searches from the right column to the left and from the top to the bottom, but is very easy to understand and use.

In this and the following lesson, the knowledge of the simple syntax of the functions mentioned, is considered a perquisite.

We have reached the end of our quest for the best lookup function, where we will talk about the combination of index and match. In the current lesson, we will use index with dual match functions, to achieve a matrix lookup functionality. But you can easily skip one of the two match functions, and replace it with a static value if you are not interested in a matrix lookup.

The knowledge of the simple syntax of the functions mentioned, is considered a prerequisite.

In Finance Excel really excels. It has a multitude of functions to cover almost any needs. 
You can find approximately 40 of them analyzed in this chapter. 
We will try to explain them to you in both simple and more financial language. They will be accompanied by simple every day examples.
The examples are simple, for purely educational reasons. But rest assured they can help you understand how to use these functions from setting up a simple household budget, to holding your own on any job position in finance. 
So let’s begin

The functions we are going to see now belong to the group of functions excel provides us with, to calculate loans and investments.

The functions we are going to see now belong to the group of functions excel provides us with, to calculate loans and investments.

Statistics has come to be one of the most useful sciences in modern times.
It is being used in everything. From simple election polls to advanced physics experiments or ground breaking medical discoveries.
It is also one of the hardest sciences to master due to the complicated math behind it.
For those of us getting bored of too much math, excel has provided.
There is a vast range of functions in excel aimed to help us make our sheet be statistically accurate without having to deal with too much of the math stuff. 
In this chapter we will try to understand and learn how to use the most popular amongst them.
You won’t become expert in statistics, because this requires, unfortunately, more math. But you will learn to cope with most statistics problems in a personal or working environment.
So let’s begin already.

It is time to move into Data analysis. We will learn about pivot tables and pivot charts. We will find out how we can use calculated fields and items. We will use slicers and learn about the data model.
Finally, we will use power pivot plugin and try to understand relationships and hierarchies.
Data analysis is a vast area of interest and we try to cover the basics for as much of this area as it is possible. Hopefully this will give you the starting point from which you can launch your exploration, and boldly go where no analyst has gone before

Reaching the end of this series of lessons, I want to ask you to rate and comment on what you have seen so far.

We want to be better at this, and you are the only ones that can help us pin point the problems in our lectures (if there are any). 

I would also like to urge you to use everything you learned in this or any other series of lessons. Knowledge not being used can never become a skill.

I hope you are satisfied with what you watched and learned, and that it was helpful in some aspects of your life.