Hi all,

Could any guru help me with the following problem.



The formula I want to implement is :

f(n) = k1 x Sigma [w(i) x exp(-n-i)/tau1] where i goes from 1 to n-1.

n are my days that are arranged in a column C.

w(i) are rows in column C and the formula f (n) needs to go into rows of colmumn E.

Some more specifics of the problem with an image of my spreadsheet was described here : https://stackoverflow.com/questions/...27055#50027055


Appreciate any help implementing an elegant code, thanks.

Hi Ron,

Am Wed, 25 Apr 2018 09:58:09 -0700 (PDT) schrieb Ron George:

f(n) = k1 x Sigma [w(i) x exp(-n-i)/tau1] where i goes from 1 to n-1.

your formula:
=$B$4*SUMPRODUCT(D2*EXP(-1*(ROW(INDIRECT($A$2&":"&C2-1)))/$B$3))
^^^^^^

C2 = 1 and C2-1 = 0
That causes the error.
Try:
=$B$4*SUMPRODUCT(D2*EXP(-1*(ROW(INDIRECT($A$2&":"&C2)))/$B$3))


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus,

Thanks a lot. I put the "C2-1" bit there make it understand that the upper limit of sigma function is n-1 where n is the day on any row. If n = 3, C2-1 = 1.

Also, do you think my formula as a whole is correct?

If I manually attempt the calculation by setting k1 = 1 and tau1 = 1 and w(1) through w(4) as 20, 30, 40 & 50 respectively, f(n) comes out very different for the first 4 terms (manual calculation vs formula generated). Please see below :

Manual Calculation for f(n) :
7.35758882
13.74308889
19.77097751
25.66730821

Formula Generated f(n) :
7.35758823
15.09644173
22.12007171
28.56587158

Does the formula have to be pasted in E2 or beginning in E3?


Thanks!

Ron

On Wednesday, April 25, 2018 at 10:24:42 PM UTC+4, Claus Busch wrote:
Hi Ron,

Am Wed, 25 Apr 2018 09:58:09 -0700 (PDT) schrieb Ron George:

f(n) = k1 x Sigma [w(i) x exp(-n-i)/tau1] where i goes from 1 to n-1.

your formula:
=$B$4*SUMPRODUCT(D2*EXP(-1*(ROW(INDIRECT($A$2&":"&C2-1)))/$B$3))
^^^^^^

C2 = 1 and C2-1 = 0
That causes the error.
Try:
=$B$4*SUMPRODUCT(D2*EXP(-1*(ROW(INDIRECT($A$2&":"&C2)))/$B$3))


Regards
Claus B.
--
Windows10
Office 2016

Hi Ron,

Am Wed, 25 Apr 2018 13:46:11 -0700 (PDT) schrieb Ron George:

Thanks a lot. I put the "C2-1" bit there make it understand that the upper limit of sigma function is n-1 where n is the day on any row. If n = 3, C2-1 = 1.

Also, do you think my formula as a whole is correct?

my understanding for your formula is a bit different.
Have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgcxMl0ftNOJuVY6Duw


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus,

I see what you are doing.

Suppose we set first 4 rows of days as 1,2,3,4 and w(1), w(2), w(3) and w(4) as 10,20,30,40 respectively and compare the results with the manually generated output.

Your formula gives

f(1) = 10
f(2) = 20
f(3) = 41.03
f(4) = 60.12

Unless I forgot how to do math, :) here is the manually done computation :

When :

n = 1, no f(1) is calculated as upper limit tends to 0. No number exists?
n = 2, f(2) = 10 x e^(-1/4) = 7.7880
n = 3, f(3) = [10 x e^(-2/4)] + [20 x e^(-1/4)] = 21.6413
n = 4, f(4) = [10 x e^(-3/4)] + [20 x e^(-2/4)] + [30 x e^(-1/4)] = 40.2183


Do you see where I'm going...?


On Thursday, April 26, 2018 at 7:21:50 AM UTC+1, Claus Busch wrote:
Hi Ron,

Am Wed, 25 Apr 2018 13:46:11 -0700 (PDT) schrieb Ron George:

Thanks a lot. I put the "C2-1" bit there make it understand that the upper limit of sigma function is n-1 where n is the day on any row. If n = 3, C2-1 = 1.

Also, do you think my formula as a whole is correct?

my understanding for your formula is a bit different.
Have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgcxMl0ftNOJuVY6Duw


Regards
Claus B.
--
Windows10
Office 2016

Claus,

Here is my understanding of the math, manually done.

Let#s say we set first 4 rows of n as 1,2,3,4.
Let's say we set first 4 rows of w as 10.20,30,40

When n = 1, computation does not exist (?)
When n = 2, f(2) = 10 x e^(-1) = 3.678
When n = 3, f(3) = 10 x e^(-2) + 20 x e^(-1) = 8.711
When n = 4, f(4) = 10 x e^(-3) + 20 x e^(-2) + 30 x e^(-1) = 14.240


The formula you provided starting with row 2 is :
$B$4*SUMPRODUCT(D2*(EXP(-1*(ROW(INDIRECT($A$2&":"&C2))-1)/$B$3)))


This gives me

f(1) = 10
f(2) = 27.35758882
f(2) = 45.09644173
f(4) = 62.12007171


See the difference? Thank you.


On Thursday, April 26, 2018 at 7:21:50 AM UTC+1, Claus Busch wrote:
Hi Ron,

Am Wed, 25 Apr 2018 13:46:11 -0700 (PDT) schrieb Ron George:

Thanks a lot. I put the "C2-1" bit there make it understand that the upper limit of sigma function is n-1 where n is the day on any row. If n = 3, C2-1 = 1.

Also, do you think my formula as a whole is correct?

my understanding for your formula is a bit different.
Have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgcxMl0ftNOJuVY6Duw


Regards
Claus B.
--
Windows10
Office 2016

Claus,

Here is my understanding of the math, manually done.

Let#s say we set first 4 rows of n as 1,2,3,4.
Let's say we set first 4 rows of w as 10.20,30,40

When n = 1, computation does not exist (?)
When n = 2, f(2) = 10 x e^(-1) = 3.678
When n = 3, f(3) = 10 x e^(-2) + 20 x e^(-1) = 8.711
When n = 4, f(4) = 10 x e^(-3) + 20 x e^(-2) + 30 x e^(-1) = 14.240


The formula you provided starting with row 2 is :
$B$4*SUMPRODUCT(D2*EXP(-(ROW(INDIRECT("1:"&MAX(1,C2-1)))-1)/$B$3))


This gives me

f(1) = 10
f(2) = 27.35758882
f(2) = 45.09644173
f(4) = 62.12007171

See the difference? Thanks.


Ron


On Thursday, April 26, 2018 at 7:21:50 AM UTC+1, Claus Busch wrote:
Hi Ron,

Am Wed, 25 Apr 2018 13:46:11 -0700 (PDT) schrieb Ron George:

Thanks a lot. I put the "C2-1" bit there make it understand that the upper limit of sigma function is n-1 where n is the day on any row. If n = 3, C2-1 = 1.

Also, do you think my formula as a whole is correct?

my understanding for your formula is a bit different.
Have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgcxMl0ftNOJuVY6Duw


Regards
Claus B.
--
Windows10
Office 2016