1. Is change on the menu?
It looks like our price changes didn't have the intended effect. But as we have discussed throughout this course, there is a measure of uncertainty and error in these calculations. Could it be that some other factor influenced sales?
2. Baking in the rain?
Maybe it rained more after we changed our prices, which kept potential customers at home? We'll add to our dataset in this lesson to see whether other factors might have played a role in the changes we saw in the sales.
3. Data overview
Before we start with the exercises, let's review the data.
You'll notice that there is a new column that shows the number of inches of rain that fell that day. Note that this is the total for the entire day, not just for the time at which the sale happened (which wouldn't be useful because rain doesn't accumulate immediately). If we calculated the sum of this column, we would be counting the daily total once for each transaction, which would inflate our totals.
Instead, we will need to use the MAXIFS() function. This function takes
the range of cells within which to find the maximum value,
the range of cells to apply the criterion,
and the criterion to apply. We can supply as many criteria ranges and criteria as we want to this function, but we usually limit ourselves to two pairs.
4. Correlation review
One way to explore the relationship between rainfall and sales is with simple correlations.
Recall that the CORREL() function takes two ranges of cells to correlate and returns a correlation coefficient.
These coefficients vary between -1 and 1 and can have a
negative or
positive relationship. Remember that negative correlations mean that as one value increases, the other decreases. Positive correlations indicate that the two values rise and fall together.
Correlations also vary in terms of their strength.
Remember that correlations between -0.3 and positive 0.3 are considered to be weak, meaning there is little relationship between the two variables.
Coefficients between 0.3 and 0.7 (either positive or negative), indicate a moderate relationship, and
correlations with absolute values larger than 0.7 suggest strong relationships between the two variables.
5. Let's practice!
Now let's get some practice using those techniques to see if rain has any bearing on sales.