Diagonal Sum Product If Formula

[sweetmetrics]

I am stumped on coming up with a dynamic formula that calculates a value based on an increasing number of "sums of products". Specifically, I need to calculate the total number of repeat users based on a "decaying' repeat rate. In other words, I need it to calculate = Sum[... +(d5*f5)+(d4*g4)+(d3*h3)]

or in equation form for you math folks like me out there:

where
n = (1, "infinity")
and
i = {1, 2, ... (n-1)}

with
"Xn" is the calculated value of repeat users for month "n" (output/result cell)
"mi" is the number of new users (in month i)
"Nn-i" is the column 'number' of the repeat rate value (%) multiplier

Here is a screenshot of the first couple rows to illustrate:

I actually have about 70 months of data (rows) and roughly the same amount of columns representing a decaying repeat rate (70 or so) and these will be increasing as time goes on so I need this formula to be dynamic (i.e. - copy it down the column of (# of repeat users)

I am using Excel for Mac 2011.

Thanks in advance for any help!

 

[shg]

Untested, but nicely done, Marcelo

 

[Marcelo Branco]

Thank you very much, shg

M.

 

[pgc01]

Thanks SHG, but I really think that a solution like yours (with the correction you posted) is the way to go. This was more for fun.

Nice one Marcelo. I was avoiding a volatile solution but if the worksheet does not have heavy calculations your formula will be much easier to implement, and will probably work for bigger tables than mine.

I'm not sure my formula would work for a big table, it might need too many resources.

 

[Marcelo Branco]

PGC

Thanks SHG, but I really think that a solution like yours (with the correction you posted) is the way to go. This was more for fun.

Agree!

Nice one Marcelo. I was avoiding a volatile solution but if the worksheet does not have heavy calculations your formula will be much easier to implement, and will probably work for bigger tables than mine.

Thank you!

Yes, I tried all ways to avoid OFFSET (volatile) but without success...

M.

 

[Marcelo Branco]

Forgot to say.

Sweetmetrics,

Thanks for the very interesting thread. Come back often!

M.

 

[shg]

The one line could be simplified (and improved) to

        SM = SM + rCol(i).Value * rCol(i, n - i + 1).Value

Yikes! I meant

        SM = SM + rCol(i).Value * [COLOR=#ff0000]rTbl[/COLOR](i, n - i + 1).Value