SCENARIO
ANALYSIS
FUNCTIONS
INDEX Function
The INDEX function is a great tool to run scenarios and is especially helpful for scenarios that
have many cases.
In cell C5, we see the function =INDEX(C7:C9,$C$1). There are two parts to this function, the
array (C7:C9), and the row number ($C$1).
The row number checks the number in cell C1 to see which value in the array (C7:C9) to
display in cell C5.
The first cell in the array will be displayed in cell C5 if the switch is set to 1, the second cell
will be displayed if the switch is set to 2, and so on.
=INDEX(array, row_num, [column_num])
fx =INDEX(C7:C9,$C$1)
If there is a 1 in C1, it displays
the value in C7 (2.0%). If there is
a 2 in C1, it displays the value in
C8 (1.8%). If there is a 3 in C1, it
displays the value in C9 (2.5%).
The benefit to the INDEX function is that you can reference a range of cells instead of clicking on
each cell individually.
OFFSET Function
The OFFSET function is a great tool to run scenarios and is especially helpful for scenarios that
have many cases and more cases will be added over time.
In cell C5, we see the function =OFFSET(C6,$C$1,0). There are three parts to this function, the
reference cell, the number of rows to drop down from the reference cell, and the number of
columns to move over from the reference cell.
The reference value, C6, is the starting point. The OFFSET will drop down one row because the
switch, C1, has been set to 1. Last, the OFFSET will stay in the same column because the third
argument is set to zero.
=OFFSET(reference, rows, cols, [height], [width])
fx =OFFSET(C6,$C$1,0)
If there is a 1 in C1, it displays
the value in C7 (2.0%). If there is
a 2 in C1, it displays the value in
C8 (1.8%). If there is a 3 in C1, it
displays the value in C9 (2.5%).
The benefit to the OFFSET function is that you can add more cases and you don’t need to change
the syntax in the formula.
CHOOSE Function
The CHOOSE function allows for multiple scenarios to be quickly toggled. The formula is simple to
use and easy to understand.
In cell C5, we see the function =CHOOSE($C$1,C7,C8,C9). There are two parts to this function, the
index number $C$1, and the values (C7,C8,C9). The index number in cell C1 dictates which value to
display.
=CHOOSE(index_num, value1, [value2], ...)
fx =CHOOSE($C$1,C7,C8,C9)
If there is a 1 in C1, it displays
the value in C7 (2.0%). If there is
a 2 in C1, it displays the value in
C8 (1.8%). If there is a 3 in C1, it
displays the value in C9 (2.5%).
The benefit to the CHOOSE function is that it is extremely simple and easy to understand by anyone
who reviews it, which creates a lot of confidence for the person reviewing the file.
XLOOKUP Function
In cell C5, we can use the function =XLOOKUP($C$1,A7:A9,C7:C9). There are three key components to
this function: the lookup value ($C$1), the lookup array (A7:A9), and the return array (C7:C9).
The function searches for the value in cell C1 within the range A7:A9, finding the value in A7, and
returns the corresponding value from the range C7:C9, with the value in C7.
=XLOOKUP(lookup_value, lookup_array, return_array)
fx =XLOOKUP($C$1, A7:A9,C7:C9)
If there is a 1 in C1, it displays
the value in C7 (2.0%). If there is
a 2 in C1, it displays the value in
C8 (1.8%). If there is a 3 in C1, it
displays the value in C9 (2.5%).
The benefit of the XLOOKUP function is that it allows you to reference a range of cells for both the
lookup and return arrays, simplifying the formula. Additionally, it provides clarity in auditing since it
clearly displays both the lookup and return ranges, making it easier to verify the data source and output.
SWITCH Function
The SWITCH function is a great alternative to using multiple nested IF statements, allowing for simplified
expressions when evaluating an expression against a list of values to return the corresponding result of the
first matching value.
In cell C5, we see the function =SWITCH($C$1,A7,C7,A8,C8,A9,C9). There are three main parts to this
function: the expression, the values, and the results. The expression is the value or cell reference to
evaluate, which in this case is $C$1. The values are the possible values of the expression to match (A7, A8,
A9). The results are the corresponding values to return (C7, C8, C9) if the expression matches one of the
specified values.
=SWITCH(expression, value1, result1)
fx =SWITCH($C$1,A7,C7,A8,C8,A9,C9)
If there is a 1 in C1, it displays
the value in C7 (2.0%). If there is
a 2 in C1, it displays the value in
C8 (1.8%). If there is a 3 in C1, it
displays the value in C9 (2.5%).
The benefit of the SWITCH function is that it simplifies decision-making logic by allowing you to check
multiple conditions in a clean, readable format. This eliminates the need for complex nested IF
statements, making the formula easier to audit and maintain.
