EXCEL CRASH COURSE END OF COURSE ANSWERS
AND QUESTIONS SURE A+
✔✔CHOOSE - ✔✔=CHOOSE(index_num, value1, value2, value3,...)
returns a number from a specified list of up to 254 values
✔✔MATCH - ✔✔=MATCH(lookup_value,lookup_array,match_type)
returns the relative position (number) of an item in an array that matches the specified
lookup value
it does NOT return the value within the cell itself (as opposed to the HLOOKUP and
VLOOKUP functions)
✔✔data validation - ✔✔data validation is a utility in Excel, whose most frequently used
feature is its ability to create simple and quick drop-down menus
to create a drop down menu:
-with the cell where you want your drop down menu active, open the data validation
form (*Alt d l*
-within the Settings tab, select list from the dropdown menu
-within the 'Source' field, identify a contiguous cell range containing the data you want to
include in your dropdown, and hit OK and you should your dropdown menu appear
note:
it only appears when you are on the active cell
✔✔building a vertical data table - ✔✔1. identify the output variable
-the variable you are trying to sensitize is the output variable
-must be referenced from your analysis into the TOP RIGHT CORNER of the data table
2. hard-code the input variable sensitivities
-the variables whose impact on the output variables you want to analyze are the input
variables
-input variable assumptions should not be referenced from the analysis, but rather be
hard-coded and arranged in the column to the right of the output variable
, 3. run the data table
-hit *Alt d t*: the Data Table dialog will appear
row input cell: not needed for vertical data tables
column input cell: reference the input variable from the model
-highlight the entire range (including the output variable) and hit OK when done - the
data table should populate
-you. may need to hit *F9* if Excel is set to "manual" or "automatic calculations except
for data tables"
NOTE:
data tables must always be in the same worksheet as the input variables
✔✔building a horizontal data table - ✔✔1. referenced output variable from your analysis
into the BOTTOM LEFT CORNER of your data table
2. input the input assumptions in the row above and one cell to the right of the output
reference
3. highlight the entire range (including the output variable) and hit *Alt d t*: the Data
Table dialog will appear
row input cell: reference the input variable from the model
column input cell: not needed
4. hit OK when done - the data table should populate
✔✔building a 2-sided data table - ✔✔same as vertical data table, but allows for 2 inputs
instead of one
output variable must be referenced from the model into the TOP LEFT CORNER of the
data table
now both row input cell and column input cell are needed
✔✔SUMPRODUCT - ✔✔=SUMPRODUCT(array1,array2,array3,...)
multiplies corresponding components in two or more arrays, and returns the sum of
those products
✔✔Booleans in Excel - ✔✔when Excel spits out a TRUE or FALSE, you can convert
them respectively into 1 or 0 by applying any operator on them
multiply the TRUE/FALSE cell by 1: will convert a TRUE to 1 and FALSE to 0
multiply the TRUE/FALSE cell by TRUE: will convert a TRUE to 1 and FALSE to 0
✔✔SUMIF - ✔✔=SUMIF(range, criteria, sum range)
adds the cells specified by a given criteria
AND QUESTIONS SURE A+
✔✔CHOOSE - ✔✔=CHOOSE(index_num, value1, value2, value3,...)
returns a number from a specified list of up to 254 values
✔✔MATCH - ✔✔=MATCH(lookup_value,lookup_array,match_type)
returns the relative position (number) of an item in an array that matches the specified
lookup value
it does NOT return the value within the cell itself (as opposed to the HLOOKUP and
VLOOKUP functions)
✔✔data validation - ✔✔data validation is a utility in Excel, whose most frequently used
feature is its ability to create simple and quick drop-down menus
to create a drop down menu:
-with the cell where you want your drop down menu active, open the data validation
form (*Alt d l*
-within the Settings tab, select list from the dropdown menu
-within the 'Source' field, identify a contiguous cell range containing the data you want to
include in your dropdown, and hit OK and you should your dropdown menu appear
note:
it only appears when you are on the active cell
✔✔building a vertical data table - ✔✔1. identify the output variable
-the variable you are trying to sensitize is the output variable
-must be referenced from your analysis into the TOP RIGHT CORNER of the data table
2. hard-code the input variable sensitivities
-the variables whose impact on the output variables you want to analyze are the input
variables
-input variable assumptions should not be referenced from the analysis, but rather be
hard-coded and arranged in the column to the right of the output variable
, 3. run the data table
-hit *Alt d t*: the Data Table dialog will appear
row input cell: not needed for vertical data tables
column input cell: reference the input variable from the model
-highlight the entire range (including the output variable) and hit OK when done - the
data table should populate
-you. may need to hit *F9* if Excel is set to "manual" or "automatic calculations except
for data tables"
NOTE:
data tables must always be in the same worksheet as the input variables
✔✔building a horizontal data table - ✔✔1. referenced output variable from your analysis
into the BOTTOM LEFT CORNER of your data table
2. input the input assumptions in the row above and one cell to the right of the output
reference
3. highlight the entire range (including the output variable) and hit *Alt d t*: the Data
Table dialog will appear
row input cell: reference the input variable from the model
column input cell: not needed
4. hit OK when done - the data table should populate
✔✔building a 2-sided data table - ✔✔same as vertical data table, but allows for 2 inputs
instead of one
output variable must be referenced from the model into the TOP LEFT CORNER of the
data table
now both row input cell and column input cell are needed
✔✔SUMPRODUCT - ✔✔=SUMPRODUCT(array1,array2,array3,...)
multiplies corresponding components in two or more arrays, and returns the sum of
those products
✔✔Booleans in Excel - ✔✔when Excel spits out a TRUE or FALSE, you can convert
them respectively into 1 or 0 by applying any operator on them
multiply the TRUE/FALSE cell by 1: will convert a TRUE to 1 and FALSE to 0
multiply the TRUE/FALSE cell by TRUE: will convert a TRUE to 1 and FALSE to 0
✔✔SUMIF - ✔✔=SUMIF(range, criteria, sum range)
adds the cells specified by a given criteria
