With each IF function, we will test if the Working hours (B2) are:

=IF(B2=1,IF(C2>=40,100,IF(C2>25,75,IF(C2>15,50,0))),IF(B2=0.75,IF(C2>=40,80,IF(C2>25,65,IF(C2>15,35,0))),IF(B2=0.5,IF(C2>=40,60,IF(C2>25,40,IF(C2>15,20,0))),0))) With each IF function, we will test if the Working hours (B2) are: Equal to : IF one of the values turns to be true, then we will be test if: The sales Value is larger or equal to 40;

If false, the sales value larger than 25 (and automatically lower than 40 since the previous statement was false); If false, the sales value larger than 15 (and automatically lower than 25 since the previous statement was false); If false, the value will be 0;

The same result can be achieved in an easier way using the “IFS” function – IFS is a nested IF function which allows for multiple tests and values if TRUE only – which means that we have to accompany it with an IFERROR function, in the eventuality that the test will not be true. =IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, …) The final formula will look like this:

And this is how the IFS function will look:

And this is how the IFS function will look: =IFERROR(IFS(B2=1,IFS(C2>=40,100,C2>25,75,C2>15,50),B2=0.75,IFS(C2>=40,80,C2>25,65,C2>15,35),B2=0.5,IFS(C2>=40,60,C2>25,40,C2>15,20)),0) An additional part of using conditional formulas are adjacent testing functions such as: AND(logical1,logical2,…) – will test each logical test and return “TRUE” only if ALL tests are TRUE;

OR(logical1,logical2,…) – will test each logical test and return “TRUE” only if AT LEAST ONE test is TRUE; NOT(logical) – will return TRUE if the test is FALSE and FALSE if the test is TRUE; IFNA(value,value_if_NA) – will return a value if a logical test returns #N/A;

All these logical tests can amount to very complex formulas, so we need to be able to properly follow the logical line when things get complicated – luckily Excel offers a handful of tools that can help us evaluate our formulas.

Remove Arrows – will remove all arrows from the previous two options;

The “Formula Auditing” section under the “Formulas” Main tab has a few functions designed to help with evaluating formulas: Trace Precedents – will draw arrows indicating all cells which affect the value of the cell currently selected; Trace Dependents – will draw arrows indicating all cells which are affected by the value of the cell currently selected;

Remove Arrows – will remove all arrows from the previous two options; Show Formulas – will temporarily eliminated the “Cell Displayed Value”, showing only the actual values of all cells; Error Checking – will launch a new menu with more options on evaluating or checking an error (only if there is at least one formula in our Excel Workbook which contains or results in an error);

Evaluate Function – will provide a step-by-step evaluation of the formula from the cell we have currently selected; Let’s break these options one by one, since they are vital identifying problems with complex formulas.

Trace Precedents

Trace Precedents Trace Dependents Each of the above cells impact the value of E9, since it contains a SUM formula. Thus, the arrows will point from each cell towards the E9 cell we have selected. Each of the cells form above (B5, D3, G3, etc.) contain the product between the value of E9 and the numerical value of each cell’s ROW. Thus, the arrows will point from E9 to all cells affected by it’s value.

Show Formulas – the cells blow (A1:C10) all contain a formula to calculate the ROW value + the COLUMN val

Show Formulas – the cells blow (A1:C10) all contain a formula to calculate the ROW value + the COLUMN value of each cell. Upon pressing the “Show Formulas” button, these cells will contain the actual formulas and not the results of the formulas.

To showcase Evaluate Function let us return to our original example:

To showcase Evaluate Function let us return to our original example:

Hiding, Deleting and Inserting Rows/Columns

Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0;

Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E. Pivot Tables, Charts and Slicers

This module deep dives into the popular (and very useful) pivot tables. Learn how you can create and modify them to solve a variety of business problems. Then gain skills to create interactive dashboards with pivot charts and slicers

PIVOT TABLES

Being able to quickly analyze data can help you make better business decisions. But sometimes it’s hard to know where to start, especially when you have a lot of data. PivotTables are a great way to summarize, analyze, explore, and present your data, and you can create them with just a few clicks. PivotTables are highly flexible and can be quickly adjusted depending on how you need to display your results. You can also create PivotCharts based on PivotTables that will automatically update when your PivotTables do.

Let us consider the following example: We have a list of raw data containing 100 agents as well as details about their location, sales, skills and the month of when the sales were registered. A pivot table will help us to quickly analyze a few stats this data.

The following pop-up window will appear:

First, we need to select the data which will provide details for the pivot table. We will then go to the “Insert” Main Tab and Select “Pivot Table”. The following pop-up window will appear: The first section will ask you for details as to where the RAW data is located. By default it’s set to take the range you had selected, prior to pressing the “Pivot Table” button

The second part will ask for details as to where we want our pivot table to be created. By default it’s set to create a new sheet.

Columns  Information in this area will act like columns in our pivot;

Pressing “OK” will bring up the following tab in a new sheet. This section will help us customize the Pivot Table fields so it reflects the information desired. This section will display the name of each column in our RAW data. This information can be dragged & dropped in the area below. This is where the magic happens. These 4 sections will help the pivot table understand what we want to do:

Filters  will act like a filter outside of the table, allowing us to switch between specific types of data; Columns  Information in this area will act like columns in our pivot; Rows  Information in this area will act like rows in our pivot; Values  the actual data which will be generated in the table;

So, what do we want from our RAW data? Let’s try these two analysis:

So, what do we want from our RAW data? Let’s try these two analysis: Total number of sales per location for skill 1 and 2; Average number of sales per month by location for skill 3;

1. First, we want the skills to act as filters so we can select 1 and 2. Thus, we will move the column “Skill” to the “Filters” area. Next, we want the location to act as Rows so we can see all 5 locations in separated rows – thus we will move “Location” to “Rows”. Lastly, we need the sales to be summed up per location, so we will move “Sales” to the “Values” area . The result will give us a simple, elegant table showcasing what we wanted with minimum effort!

By default, moving information to the “Values” field will generate a SUM of that data. However, this, can

By default, moving information to the “Values” field will generate a SUM of that data. However, this, can be modified to do all sorts of functions. Let’s see example 2:

Another very beneficial tools of Pivot Tables are Slicers (these can also be used for regular tables). By

Another very beneficial tools of Pivot Tables are Slicers (these can also be used for regular tables). By using the “Insert Slicer” option under the “Analyze’ Main Tab, we can select a column of the RAW data we want filtered and this will generate a “control panel” which allows for fast & easy filtering

PIVOT CHARTS

PIVOT CHARTS Just as PivotTables behave similar to regular tables but providing extensive options for customizing the data, Pivot Charts will allow for multiple level filtering of information and real-time chart modifications.

Let’s add a PivotChart to the previous example. To do this, all we have to do is go to the “Analyze” Main Tab and select “PivotChart”. What this will do is to open a template box from where we can choose the type of chart we want.

Manipulating data in a pivot table is extremely easy and to make it even better, Excel offers a handful o

Manipulating data in a pivot table is extremely easy and to make it even better, Excel offers a handful of options to customize Pivot Tables as well as insert Pivot Charts.

Let’s see how a chart will help us manipulate and understand data better on the previous example. How hard is it to see which Location had the highest average of sales in a month for skill 3? Or who had more sales in the month of April between London and Bucharest?

Hiding, Deleting and Inserting Rows/Columns

Hiding, Deleting and Inserting Rows/Columns Before discussing these functions we need to understand the notation of rows/columns in Excel. These notations are static and cannot be changed by addition or removal of any number of rows/columns. Hide – this option hides any number of rows or columns selected by reducing their width to 0;

Delete – deletes all selected rows/columns and redistributes the notation for the others Insert – Inserts one or more rows/columns on the left (for columns) or above (for rows) the selected row/column. If we delete columns A through E, all the information in the other columns will move left but the letters for the first 5 columns will still be A through E. Automating Lookups

How do find information from different parts of a workbook? This module introduces you to one of the most important excel functions: VLOOKUP

Consider the following example:

VLOOKUP is an essential excel formula that can be found in many of our daily files. The formula is used to look a value inside a table and extract a corresponding value from the same row. Consider the following example: On one side (Columns B and C) we have an exported table containing the number of sales for each agent.

On the other side we need to extract the information for each one of our agents which are split in 4 teams Speaker notes: Vlookup works only as long as the Lookup info is on the FIRST column of the array selected!

We’re going to start writing our VLOOKUP formula in F3 for the first agent.

We’re going to start writing our VLOOKUP formula in F3 for the first agent. VLOOKUP uses a very simple functioning principle – you provide a value and a table which has that value on it’s 1st column – once the formula finds the value, it will return a result which is on the same row as the one you provided, but on a different column VLOOKUP has 4 major elements

Lookup_Value – the actual value we are trying to find in a table; Table_Array – the table where our values are located(both the LookupValue and the value we want in return); Col_Index_Num – the number of columns (to the right) where the wanted data is (count starts with 1) [Range_Lookup] – 0 for an Exact Match or 1 if you want the lookup to be an approximate value;

B3 – Lookup_Value

B3 – Lookup_Value B:C (or B2:C22) – Table_Array 2 – Col_Index_Num since the value we want back is on the 2nd column of the table 0 – [Range_Lookup] because we want an exact match =VLOOKUP(E3,B:C,2,0) Speaker notes: It’s very important to remember that VLOOKUP will only work if the value we look for can be found on the first column of the Table_Array

We can then apply the formula to the other fields of TEAM 1. Next we will apply it to the other team tabl

We can then apply the formula to the other fields of TEAM 1. Next we will apply it to the other team tables but beware, copying the formula from one table to another will move all unblocked references! The first example showcases the situation of copying the formula without blocking the Table_Array reference.

When copying the formula to the TEAM 2 table, both the Lookup_Value and the Table Array will be moved 3 columns to the right. While we want that to happen to the Lookup_Value (since we don’t want to look for the same agents we did in the TEAM 1 Table) this will result in an error since the Table_Array will be completely out of place.