correlation of 2 datasets with exact same values

CFA Level II Quant
#1

Say we have two datasets with exact same values and zero variability, set A (10,10,10,10), set B (10,10,10,10)

The standard deviation of each is zero, the covariance between the 2 is 0. Thus correlation should be (0/(0*0))=undefined, yet excel formula for correlation still returns 1. Now 1 seems like the correct answer, however you cant get to it with the above math… How did excel arrive at 1 and what is the correlation in this case…

Thanks,

#2

Excel formula doesn’t return 1. It gives #DIV/0! . Correlation is not defined. Think it this way. If one of your SD’s for a data set is 0 (here both are 0), then correlation equation is undefined. Theoretically, a correlation is telling you about how one variable moves around as the other variable moves around. And SD of 0 implies that variable is not moving around, hence there is no point of calculating it’s correlation with any other data set.

#3

Data is not stochastic but deterministic. So no point in estimating correlation