Module 2 Lagniappe

Supplement to Module 2 PWC Excel Case Study/Course.

PWC Excel Case Study

Information not discussed in class

“Anscombe’s quartet”

\(\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

Visualizing the quartet

Index function

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

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

Index/Match

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.

Optimization

  • Goalseek provides a way to solve simple equations

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

Solver - Objective and constraints