Rolling 12 Numbers Calculation

[bapswarrior]

Hi Guys, Really need help, i was given this question for my interview and it has haunted me ever since.

Question is to compute beta i.e slope, i.e covariance/variance. But that is not the problem. This issue is as follows.

I have 2 columns which has data. The data is monthly. Some months have data, some have blanks/zero. I would like a formula that would automaticall pick up the previous 12 NUMBERS (skipping blanks). So my forumla should be =covs(this is where i want the magic to happen)/vars(this is where i want the magic to happen). I have a coloumn in which the numbers are all positvie and a different one where numbers are all negative. Below is a sample, what i would like is the formula to pick up last 12 number so that would be 1 through 13 formula 2 would be 2-14 and so on.

A B
1 1

2 2
3 3
4 4

6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
14 14 FORMULA
FORMULA


Any help on this would be a job save

Thanks.

 

[Debraj]

Hi baps warrior,

welcome to the forum..
can you please upload a sample file.. I am lil bit confused.. with covs/vars statement.

For the time being..
play with this..
its still not completed as it will cover spaces also..

i am working on it.. to exclude blank cells .. and in the meanwhile.. you upload a sample file..

=SUM(INDEX(A:A,MATCH(99^99,A:A)-11):INDEX(A:A,MATCH(99^99,A:A)))

 

[Luke M]

Taking the problem as "previous 12 numbers" and thinking there may be numbers after the point, array formula in row 14 of your example:
=SUM(A14:INDEX(A:A,LARGE(ISNUMBER(A$1:A14)*ROW(A$1:A14),12)))

I've encased the array inside a SUM function, but you should be able to put it into a COVAR or VAR function as well.

 

[Sam Mathai Chacko]

Here's one way of doing it using array formulas. There must be a better formula, but just a bit lazy here :D

=SUM(INDIRECT("A"&MATCH(12,COUNTIF(INDIRECT("A"&ROW($1:31)&":A"&ROW(A31)),"<>"),)&":A"&ROW(A31)))

if you are on the 32nd row

 

[bapswarrior]

Deb, Luk and Sam THANKSSSSSS, I used Luk's but will test out the rest later. I can't believe i spent 4hrs and couldn't come even close. This made the trick. YOU GUYS ARE GENIUS! How can i become awesome like you guys? I hopefully (crossfingers) will have a position that involves excel and i am like upper beginner. Do you guys have any tips? Also any help on trying to understand that formula? I am going to search online and dig up the basics.

 

[Luke M]

Thanks bapswarrior. We've had a lot of practice.

Recent article on INDEX:
http://chandoo.org/wp/2013/09/18/index-formula-usage-and-tips/
Array article with criteria:
http://chandoo.org/wp/2010/10/08/large-if-array-formula-tutorial/