Supplement to Module 2 PWC Excel Case Study/Course.

\(\color{red}{\text{Property}}\) | \(\color{red}{\text{Value}}\) | \(\color{red}{\text{Accuracy}}\) |
---|---|---|

Mean of x | 9 | exact |

Sample variance of x | 11 | exact |

Mean of y | 7.5 | 2 decimal places |

Sample variance of y | 4.125 | plus/minus 0.003 |

Correlation between x and y | 0.816 | 3 decimal places |

Linear regression line | y = 3.00 + 0.500x | 2-3 decimal places |

- An alternative approach that offers greater flexibility is to use a combination of the INDEX and MATCH functions.
- INDEX returns the value at a particular position (index) in a range

**Syntax**

=INDEX ( \(\color{red}{\text{range of values}}\), \(\color{maroon}{\text{row number}}\), \(\color{green}{\text{column number (optional)}}\) )

**Example**

=INDEX (\(\color{red}{\text{B2:B6}}\), \(\color{maroon}{\text{4}}\))

Returns a value of 40,000, because 40,000 is in the fourth row of the range.

- MATCH is used to find which row or column in a list contains a particular value
- Typically the match type should be set to zero for an exact match

**Syntax**

=MATCH ( \(\color{red}{\text{value to find}}\), \(\color{maroon}{\text{range to search}}\), \(\color{green}{\text{match type (optional)}}\) )

**Example**

=MATCH (\(\color{red}{\text{"Grade 3"}}\), \(\color{maroon}{\text{A2:A6}}\), \(\color{green}{\text{0}}\))

Returns a value of 3, because ‘Grade 3’ is in the third cell of the range

Example: Find the unit cost based on the number of units

First we use a MATCH function to find the appropriate row based on the (approximate) number of units:

- =MATCH(\(\color{red}{\text{C3}}\),$B$8:$B$12,\(\color{maroon}{\text{1}}\))

Then we use an INDEX function to retrieve the cost value contained in this row:

- =INDEX($C$8:$C$12, row number goes here )

These combine to make an overall formula:

- =INDEX($C$8:$C$12,MATCH(\(\color{red}{\text{C3}}\),$B$8:$B$12,\(\color{maroon}{\text{1}}\)))

In this example, the number of units is 595, and the formula returns $10.

Goalseek provides a way to solve simple equations

- Set a cell to a value
- Change another cell to produce the set value

Solver provides more powerful and scalable optimization for systems of equations