Currently I have a spreadsheet where A1 & V1 stock symbols, and the cells that follow, B1, C1, D1 are closing prices for a set number of days
So think of A1 as an array variable and B1:E1 as the elements. Same for V1 and W1:Z1 as elements
A1 B1 C1 D1 E1... V1 W1 X1 Y1 Z1...
And I am calculating the following:
Return:
DC2 = C1-B1/X1-W1, DD2 = D1-C1/Y1-X1, DE2 = E1-D1/Z1-Y1... and then AVEDEV(DC2:**2)*SQRT(PI()/2)
Because I am only comparing two specific stocks, I am limited in this case, to A1 and V1 but would like to have more choices (think A253/V72, etc.). However, I am looking to have only one column of stock symbols with
trailing elements and run these calculations against any two stocks within the same column.
Suppose the following:
A1 B1 C1 D1 E1...
A2 B2 C2 D2 E2...
A3 B3 C3 D3 E3...
1) I want to be able to Index/Match A1 with C1-B1, D1-C1, E1-D1... A2 with C2-B2, D2-C2, E2-D2...
2) Index/Match A1/A2 with C1-B1/C2-B2, D1-C1/D2-C2, E1-D1/E2-D2... A1/A3, A2/A1, A3/A2, etc..
I am not sure how to implement this. Is this where you would use a pivot table/matrix/something else? I've never created anything like this before. As my spreadsheet becomes more complex and my universe of 'arrays' grows I'd like to be able to easily pick and choose which two arrays to compare.
You could imagine that I am a little anxious here as I am having a tough time even explaining it.
Thanks for looking.
So think of A1 as an array variable and B1:E1 as the elements. Same for V1 and W1:Z1 as elements
A1 B1 C1 D1 E1... V1 W1 X1 Y1 Z1...
And I am calculating the following:
Return:
DC2 = C1-B1/X1-W1, DD2 = D1-C1/Y1-X1, DE2 = E1-D1/Z1-Y1... and then AVEDEV(DC2:**2)*SQRT(PI()/2)
Because I am only comparing two specific stocks, I am limited in this case, to A1 and V1 but would like to have more choices (think A253/V72, etc.). However, I am looking to have only one column of stock symbols with
trailing elements and run these calculations against any two stocks within the same column.
Suppose the following:
A1 B1 C1 D1 E1...
A2 B2 C2 D2 E2...
A3 B3 C3 D3 E3...
1) I want to be able to Index/Match A1 with C1-B1, D1-C1, E1-D1... A2 with C2-B2, D2-C2, E2-D2...
2) Index/Match A1/A2 with C1-B1/C2-B2, D1-C1/D2-C2, E1-D1/E2-D2... A1/A3, A2/A1, A3/A2, etc..
I am not sure how to implement this. Is this where you would use a pivot table/matrix/something else? I've never created anything like this before. As my spreadsheet becomes more complex and my universe of 'arrays' grows I'd like to be able to easily pick and choose which two arrays to compare.
You could imagine that I am a little anxious here as I am having a tough time even explaining it.
Thanks for looking.