Create Dropdowns in Excel

You can create a dropdown using the Data Validation in Excel . 

Goto  Data Tab -> Data Validation

In the Allow, Select List and in the Source give the range you want to appear in the dropdown .

You can create a dynamic list by using a combination of "Counta and Offset"

In the Source you can enter =OFFSET($D$3,1,0,COUNTA($D$4:$D$12),1) , The $D$3 acts as the starting point, Counta provides the name the total count of values entered from D4:D12 , so any new value entered is picked up automatically . 

Hope this helps 

Happy Learning

Basic excel for beginners Part-1

Basic Excel for Beginners

Excel is one of the most commonly used tool across all companies , no matter how much tech savvy  you become you will always use excel for basic modelling , publishing reports , storing information etc. So for those who have only used excel only as a phone directory so far this could be a very useful post.

Let's begin

First lets familiarize ourselves with the layout of an excel sheet. 

This gives you the list of basic things the excel screen contains . I am not going to go in the details of each thing as they are quite self explanatory. Lets jump into our first hands on with excel.

Time Savers using commonly used Excel shortcuts

The most important rule in using excel is using keyboard as much as possible . The lesser you use the mouse for small works the more proficient in excel you will become. These excel shortcuts will help you become more proficient in using excel. Here are the most commonly used shortcuts.

Commonly Used Excel shortcuts

How to use this excel shortcuts eg. if I want to move to the leftmost end of the data from any point I press Ctrl+Left Arrow , similarly if I want to move down I use Ctrl+Down Arrow , if I want to move between worksheets I use Ctrl+ Page Up for previous worksheet ,Ctrl+Page Down for next worksheet. Similarly we can use the other shortcuts as mentioned in the Table . As you master these excel shortcuts working on excel sheets becomes super-fast.

Power of cell referencing

 Cell referencing is a very important feature. To freeze a cell you put dollar sign next to it so if we have a cell C15 and we want to make sure when we drag the formula it remains unchanged so we put $C$15 ( you can use the F4 key ) this is called absolute referencing. You can also freeze rows or column separately $C15 where column cannot move and row is free to move so if you drag the formula across rows it changes the row but column remains same similarly the other way round C$15 would freeze row and change column.

Using IF function

If function is a real life if something is true then what we do and if it false what we do.

Syntax: =IF(Condition,value if true , value if false)

Lets say if a cell C15 has value 100 then we give it 1 else 0 so we write it like this

=IF(C15=100,1,0) , other eg.

-if value is equal =IF(C15=100,1,0)

-if value is greater than equal too=IF(C15>=100,1,0)

-if value is not equal too=IF(C15<>100,1,0)

Using IF function with logical operators OR , AND

Syntax for each 
AND ( cond1, cond2 ,...)
OR (cond1,cond2,....)
NOT(condition)

eg using AND: if C15 is less than 100 and greater than 80 than give 1 else 0
=IF(AND(C15<100,C15>80),1,0)

eg using OR: if C15 is greater than 100 or less than 80 than give 1 else 0
=IF(OR(C15>100,C15<80),1,0)

Nested IF.

Nested IF is using IF within a if function . Using multiple ifs in the same formula
Eg : Assume C15 cell contains Age then we have to group it like this if Age<10 then Group1, Age between 10 and 25 - Group2 , Age Greater than 25 -Group 3

=IF(C15<10,"Group1",IF(AND(C15>=10,C15<=25),"Group2", IF(C15>25,"Group3", "No Group")))

Here we see if a condition is false we move on to the next condtion . Like if it is not in group1 then we check if it is group2.

SUMIFS and COUNTIFS

When we want to count records based on some conditions we use Countifs, similarly if we want to sum up something on a condition we use Sumifs.

Syntax For: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Where criteria_range1 is a mandatory,the first range in which to evaluate the associated criteria, criteria1 is mandatory, it is the criteria in the form of a number, expression, cell reference, or text that define which cells will be counted eg, criteria can be expressed as 90, ">90", B4, "apples", or "80". criteria_range2 is an optional parameter and can be added if there are multiple conditions

Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Where sum_range is range to sum (always remember its the first range in sumifs and mandatory). Criteria_range1 is mandatory and refers to the range which has criteria, Criteria1 is the criteria that is to be used criteria can be expressed as 90, ">90", B4, "apples", or "80". Rest is optional and we can add as many number of criterias as possible.

To calculate the count of Group1 , we use =COUNTIFS($A$2:$A$10,$A$2) , where A2 is the criteria which says group1, we can also use =COUNTIFS($A$2:$A$10,"Group1")

To calculate the sum of Group1 , we use =SUMIFS($B$2:$B$10,$A$2:$A$10,A2) , where B2:B10 is the sum range which is always number and A2 is criteria again you can write it as text
=SUMIFS($B$2:$B$10,$A$2:$A$10,"Group1")

Now adding one more criteria lets say for group1 where money > 5000
=COUNTIFS($A$2:$A$10,"Group1",$B$2:$B$10,">5000")
=SUMIFS($B$2:$B$10,$A$2:$A$10,"Group1",$B$2:$B$10,">5000")

Data retrieval using lookup funtions

Now suppose if we have a list of companies and there market capitalization next to them and we want to find a market cap of some particular company we filter again and again to avoid that we can use functions called Vlookup and Hlookup 

Syntax: VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) 

Lookup value is the value you want to lookup in the array.The table array is the array where you want to look ,please note for Vlookup to work the first column should contain the lookup_value. The column number (starting with 1 for the left-most column of table-array) that contains the return value. For range_lookup you can put FALSE always as it looks for exact match.

Eg: We have to find the age and salary of EMP5

For Age: =VLOOKUP("Emp5",$A$1:$C$12,2,FALSE)---->(Col_index is 2)

For Salary: =VLOOKUP("Emp5",$A$1:$C$12,3,FALSE)---->(Col_index is 3)

Syntax: HLOOKUP (lookup_value, table_array, Row_index_num, [range_lookup]) 

Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find. The H in HLOOKUP stands for "Horizontal."

Eg: We have to find the age and salary of Emp1

For Age: =HLOOKUP("Emp1",$A$1:$E$3,2,FALSE)---->(row_index is 2)

For Salary: =HLOOKUP("Emp1",$A$1:$C$3,3,FALSE)---->(row_index is 3)

Applying Vlookup for two columns

Now to apply Vlookup to two columns the best way is to form a unique key . For eg:

if I Vlookup for the salary of Andrew I will get 20,000 as answer . But I wanted to check salary of Andrew Flintoff and not Andrew Hall so to avoid that I create a key by concatenating the first and the last name(Eg: ="fname" & " " & "lname") and now I can Vlookup on the key . Make sure the key is always the first column which is the requirement forVlookup as discussed earlier .

This is very useful as we can combine n number of column and create a unique key and perform Vlookup on it. There are other ways to Vlookup on more that one condition we can discuss this later.

We will discuss more useful formulas in Part2.

Calculating working days in excel

Calculating working days in Excel

When you want to see the total number of days your employee work in a month or in a year , you do manual calculations putting a rough estimation of how many Saturdays, Sundays and N number of holidays would be there in a period and get an approximate number of working days . Not to worry because Excel does provide you with an option to calculate the working days in excel from a particular start date to end date, excluding Saturdays ,Sundays and holidays.The function that we are going to use for this purpose is called NETWORKDAYS.INTL . 

The function returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays

Syntax

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

The start date is the date you want to start from and end date is the date you want the working days till. Now the weekends is an optional parameter and is basically the days we don't include in the working days, it can take values like 1- for taking sat,sun as weekend , 2- Sun ,Mon etc. Holidays is again an optional parameters which is a list of non-working days.

Using the function

Now we take the example we create two list one the list of holidays in the year, list of start and end month for each date (Please note its just an example you can tweak as you like) as shown in the image below.

So now the days calculation has three parts one normal days difference i.e nothing but End Date-Start date. Now for the second column we use the function Networkdays.intl,in column I

=NETWORKDAYS.INTL(E6,F6,1)

Where E6 is the start date , F6 is the end date and 1 represents Saturday and Sunday, you can tweak it as you like it has 19 options for eg. if you are in Dubai you can use 7- where weekends are Friday and Saturday.

Notice the answers gives us 22 which is basically all the weekdays in a month. Now the next part is excluding the holidays also for that we use the formula in column J

=NETWORKDAYS.INTL(E6,F6,1,$C$6:$C$17)

The C6:C17 is nothing but the list of holidays, you can add as many as you like . Notice that in January the working days were 22 and now after removing holidays it is 20 and similarly for other months.

Use case

This function is really helpful for those you want to calculate the working hours in a company from a particular date . Calculating the effective utilization of the firm etc.

What-if-Analysis for Excel Part 3- Scenario Manager

What-If-Analysis - Scenario Manager

In this last session of what-if-Analysis for Excel we are going to discuss Scenario Manager. In case you missed the first two part you can use the link below to view them

For using Goal Seek in Excel use the link below -
http://manishexcel.blogspot.in/2015/09/what-if-analysis-for-excel-part-1-goal.html

For using Data Tables in Excel use the link below -

http://manishexcel.blogspot.in/2015/09/what-if-analysis-for-excel-part-2-data.html

Scenario Manager

Scenario Manager allow you to create a series of possible drivers to show in your model. The effect on Many variable on the model for different scenarios

Using Scenario Manager

The problem we are going to discuss is the same.

Where quantity is quantity sold , Unit Price is the cost to make each item , cost price is selling price . Revenues is Quantity * Cost price , CoGS(cost of goods sold) is -Quantity* Unit Price , fixed cost is some cost to produce items. Assuming we dont have any other expense or profit the

Net Profit=Revenues + CoGS+Fixed Cost

Now what we want to see is how would Net Profit change if we change other input variables and we want to store result of each change.The steps are as follows.

1.Click the Datatab, click What-if-Analysis, and choose Scenario Manager . The scenario Manager Dialog is displayed below

2.The Scenario Manager dialog box is shown below.

3. Click on Add to add scenarios.

4. Now in the box scenario name click give the name of the scenario , in our case we will call it BreakEven( Net Profit is 0). Next add the cells you want to change in Changing Cells you can select a full array or can give them one by one, in my case I am giving it as D2(Quantity), D4(Unit_price) and D5(Cost Price) as shown above.Once you click OK you get a window called Scenario Values which will show you the current values of the three cells you entered. Now you can change the values according to your scenarios and make as many scenarios as you want.

5. Once you have created scenarios all the scenarios will be listed one by one as shown below, you can edit , delete or view them one by one by using show.

6. Now to view all the scenarios at once and see how they are impacting revenue ,CoGS and net profit. You can click on Summary button .This button will ask you what you want to display in the summary , basically all the values you want to see the change on.Enter all the cells you want to see in summary in the Result cells as an array or separated by comma as shown below. Now select the report type ,in our case we have selected the scenario summary which gives me a plain and simple report . Click OK

7.Finally you will see a new sheet automatically created called Scenario Summary and would show the impact on all the result cells.Here is the look on our scenario summary sheet.

As you can see the Scenario Manager is really useful in seeing the impact of variables and also helps to store them in form of scenarios which can be later edited according to the users need . It is a very powerful tool and is often used in financial analysis to display different scenarios.

Hope this article was useful . That concludes our what-if-Analysis for excel. Do look out for more excel tips and also let me know if you want me to write on something specific.

What-if-Analysis for Excel Part 2- Data Tables

What-if-Analysis

By using three Excel what-if analysis tools, you can experiment with different sets of values in your driver cells and explore how they affect the outcomes in different formulas. In the first part we discussed Goal Seek for Excel and in this article we will discuss Data Tables for Excel , if you missed the first part of What-if-Analysis goal seek for excel please go through it using the link below

http://manishexcel.blogspot.in/2015/09/what-if-analysis-for-excel-part-1-goal.html

What are data Tables for Excel

Data tables allow you to see the impact of one or two drivers on the output of a model, and display the results in a single table. Data tables are used to conduct a sensitivity analysis.

Using Data Tables

You can create data tables with one or two variables. Data tables must be on the same worksheet as the drivers themselves.I am going to talk about the two variable type as that is more frequently used for sensitivity analysis

The problem that we are going to solve is discussed below-

Where quantity is quantity sold , Unit Price is the cost to make each item , cost price is selling price . Revenues is Quantity * Cost price , CoGS(cost of goods sold) is -Quantity* Unit Price , fixed cost is some cost to produce items. Assuming we dont have any other expense or profit the

Net Profit=Revenues + CoGS+Fixed Cost

Now what we want to see how does the Net Profit change if we change Cost Price and the quantity simultaneously. This could be done by using Data Tables for Excel.

In a double variable table, you use both column and row oriented variables.

1.Create a link to the output cell: in a blank area of the worksheet, click in a blank cell and type ‘=‘, then click the desired output cell and press[Enter]. In this case we would link it to the Net Profit

2.Create a list of different input variables to run through the output formula, directly beneath the output cell and another list of input variables directly to the right of the output cell.

3.Highlight all of the cells that contain the formula reference and the variables

4.Click the Datatab, click What-if-Analysis, and choose Data Table. The Data Table dialog box is displayed.

5.Enter the Row input cell (i.e., the cell into which you want to substitute the row-oriented variables) . In our case it is the Quantity. Tip:You can click outside the Data Table dialog box and click the appropriate driver cell and it will be automatically populated in the Row input cell field.

6.Enter the Column input cell (i.e., the cell into which you want to substitute the column-oriented variables).In our case it is Cost Price.

7.Click OK 
   The impact of the two variables on the output cell is displayed in your data table.
     Note:If your table values are all the same value, your Optionsettings may require you to press [F9]to recalculate Data        Tables.

The Final output shows the relation between cost price and the quantity . You can also see the trend both follow by using Conditional formatting. The data table after creation can also be edited I can change the cost price from 6 to 7 and the change would be instantly reflected we don't need to update the table manually . Data tables are really helpful when we are seeing the impact of two variables on the third variable.We can store each change on variable separately by creating scenarios using Scenario Analysis which we will discuss in the next part .

For using Scenario Manager in Excel use the link-
http://manishexcel.blogspot.in/2015/09/what-if-analysis-for-excel-part-3.html

What-if-Analysis for Excel Part 1- Goal Seek

Introduction

By using three Excel what-if analysis tools, you can experiment with different sets of values in your driver cells and explore how they affect the outcomes in different formulas. Here we are going to discuss the first one goal seek

Goal Seek allows you to find the necessary input value when you already know the desired output value; effectively, you start with the output value and work backwards.

Using Goal Seek

If you know the result you want from a formula, Goal Seek will help you determine the value that the driver input cell needs to be to get that result. Goal Seek can only work with one variable.

Where quantity is quantity sold , Unit Price is the cost to make each item , cost price is selling price . Revenues is Quantity * Cost price , CoGS(cost of goods sold) is -Quantity* Unit Price , fixed cost is some cost to produce items. Assuming we dont have any other expense or profit the

Net Profit=Revenues + CoGS+Fixed Cost

Now what we want to see is how many quantities we should sell in order to breakeven(Net Profit=0), so basically we need to start working backwards from Net Profit to quantity. To do that we use Goal Seek .

Steps to use goal Seek

1. Click the Data tab, click What-if-Analysis, and choose Goal Seek The Goal Seek dialog box is displayed.

2. Here is the Goal Seek Window

3. Enter the Set cell(i.e., the output cell in our case Net Profit)Tip: You can click outside the Goal Seek dialog box and click the appropriate output cell and it will be automatically populated in the Set cell field.

4. Enter the To value (i.e., the desired output in our case it is 0)

5. Enter the By changing cell (i.e., the cell you want to change to get the desired result, in this case Quantity)

6. Click OK 

7. Goal Seek will change the By changing cell input and overwrite the existing value; you can click       Undo to revert to the original value.

Final Result using goal Seek

Goal Seek is a powerful feature in excel that helps us to track the formula backwards and get the desired output.The only limitation it works on One variable at a time . To change two variable we use Data Tables which we will discuss in the next part.


For using Data Tables in Excel use the link below -
http://manishexcel.blogspot.in/2015/09/what-if-analysis-for-excel-part-2-data.html

For using Scenario Manager in Excel use the link-
http://manishexcel.blogspot.in/2015/09/what-if-analysis-for-excel-part-3.html

Calculate Minimum in excel excluding zero

Excel Minimum excluding zero

Calculate minimum using Min Function excluding zeros in Excel

Excel has a built in formula/function that makes finding the minimum value in a range of cells easy. If we assume your numbers are in A1:A100 you would simply use the MIN formula like shown below;
=MIN(A1:A100)

Now if we have a dataset as show in the image-:

Now if I were to find the minimum price bid using Min function , the answer you would get is 0 to get the correct answer we have three ways one using small function,Array formula and other using filters and subtotals

Using Small Function

By far the most efficient method is to use the SMALL and countif as shown below;

SMALL Returns the k-th smallest value in a data set. Lets say the array is in column A

=SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)

Where the countif is counting the zeros in the range (+1) and is used to tell SMALL to return the k-th smallest value.

Using Array Function

Next best method is to use array formulas . Now suppose your array is in column A , the formula you would use is

=MIN(IF($A$1:$A$100 >0,$A$1:$A$100))

As these are array formulas they MUST be entered via Ctrl+Shift+Enter. After this you will the answer and curly brackets next to the formula

Using Filters and Subtotal

Next best method is to use filters and subtotal function . Now suppose your array is in column A , the formula filter values (Alt + D+F+F) and remove 0 from the set , now use subtotal function 

=SUBTOTAL(5,$A$1:$A$100)

This will give you the minimum value in the filtered array , you could also use this function for many other operations on a filtered array

Hope this helps