Growing sum of changing values

[dpa]

Hi,
I have data from B1 to B80. To process this data, I have several parameters called Fixtime, Changetime

Fixtime is chosen by the user (lets assume a value of 2)
Changetime starts in 0.5

The general formula i need to use would be (which increases as cell increases): Results = Value_of_B*Changetime*Fixtime

As an example, in excel I can simply create a cell formula to obtain the results I need:

D1 = B1*0.5*2
D2 = B1*1.5*2 + B2*0.5*2
D3 = B1*2.5*2 + B2*1.5*2 + B3*0.5*2
D4 = B1*3.5*2 + B2*2.5*2 + B3*1.5*2 + B4*1.5*2
D5 = B1*4.5*2 + B2*3.5*2 + B3*2.5*2 + B4*1.5*2 + B5*0.5*2
.
.
D80 = .....

Note how the values of 0.5 (Changetime) are varying as the next D cell is required, each time one additional value is summed and at the same time "changetime" values increase.

As you can imagine, to do this up to D80 is annoying and takes time, once is there, is easier, but is not efficient.

I was wondering if any of the vba experts would have a cheap, simple and fast solution of a code to fill D1 to D80 and by doing the same exact calculations as in the example I presented, my problem is basically on how to loop (though I heard looping was not recommended as it is slow) to achieve the desired calculation.

Hope you can help.

Thanks in advance.

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT($B$1:B1,((ROW()-ROW($B$1:B1))+0.5)*2)

in D1 and drag down.

 

[dpa]

Hi,

Try:

=SUMPRODUCT($B$1:B1,((ROW()-ROW($B$1:B1))+0.5)*2)

in D1 and drag down.

Hi, thanks for your fast answer. Sadly I cannot make it work, I tried something like:
assume B values B1 to B10: 4, 3, 5, 2, 8.
The results I need are:
B D
4 =4*0.5*2 = 4
3 =4*1.5*2 + 3*0.5*2 = 15
5 =4*2.5*2 + 3*1.5*2 + 5*0.5*2 = 34
2 =4*3.5*2 + 3*2.5*2 + 5*1.5*2 + 2*0.5*2 = 60
8 =4*4.5*2 + 3*3.5*2 + 5*2.5*2 + 2*1.5*2 + 8*0.5*2= 96

So B has the followin resulting values in D:
B D
4 4
3 15
5 34
2 60
8 96

Any other suggestions? maybe with a VBA code?

Thanks again
cheers

 

[fairwinds]

Hi,

That is exactly the result I get. What do you get?

 

[dpa]

Hi,

That is exactly the result I get. What do you get?

My appologies, I didnt realize that automatic calculations were off, sorry. Yes, I get the same results, thanks a lot, I will try for the whole chart. Thank you!

 

[dpa]

Hi,

Try:

=SUMPRODUCT($B$1:B1,((ROW()-ROW($B$1:B1))+0.5)*2)

in D1 and drag down.

Hi again, one question. What would you suggest if I have to use the same principle but with a gamma distribution, for example:

D1 = B1*GAMMA.DIST(0.5,1,2,0)

D2 = B1*GAMMA.DIST(1.5,1,2,0) + B2*GAMMA.DIST(0.5,1,2,0)

D3 = B1*GAMMA.DIST(2.5,1,2,0) + B2*GAMMA.DIST(1.5,1,2,0) + B3*GAMMA.DIST(0.5,1,2,0)

D4 = B1*GAMMA.DIST(3.5,1,2,0) + B2*GAMMA.DIST(2.5,1,2,0) + B3*GAMMA.DIST(1.5,1,2,0) + B4*GAMMA.DIST(0.5,1,2,0)

D5 = B1*GAMMA.DIST(4.5,1,2,0) + B2*GAMMA.DIST(3.5,1,2,0) + B3*GAMMA.DIST(2.5,1,2,0) + B4*GAMMA.DIST(1.5,1,2,0) + B5*GAMMA.DIST(0.5,1,2,0)
.
.
D80 = .....

How would be the formula in this case. And one more question, is it possible to do this in a vba code. I have several "B" data values in several sheets and I need to run the procedure according to the user wishes.

Thanks again

 

[fairwinds]

Not sure at all about this...

What do you think about the result from:

=SUMPRODUCT($B$1:B1,GAMMADIST((ROW()-ROW($B$1:B1))+0.5,1,2,0))

 

[dpa]

Not sure at all about this...

What do you think about the result from:

=SUMPRODUCT($B$1:B1,GAMMADIST((ROW()-ROW($B$1:B1))+0.5,1,2,0))

It works like a charm. Thank you so much!!
What about the idea about a vba code that does the same thing?

 

[dpa]

Not sure at all about this...

What do you think about the result from:

=SUMPRODUCT($B$1:B1,GAMMADIST((ROW()-ROW($B$1:B1))+0.5,1,2,0))

hi again, I have a new challenge related to the same situation, instead of a gamma distribution in the same way with sumproduct. i tried to create the formula and use it in the same way as the gammadistribution but I am getting an error.

The formula I tried is:

in VBA:
Function MyFormula(val As Double, fix1 As Double, fix2 As Double)
MyFormula = fix1 * (1 / fix2) * (Exp(-((val * fix1) / fix2) + (fix1) - 1))
End Function

This time, the value that changes from 0.5 and increases on every row is the value called val, so I tried this for D1 to D80:

In Sheet:
=SUMPRODUCT($B$1:B80,MyFormula((ROW()-ROW($B$1:B80))+0.5,2,2))

But when I try to copy from D1 to D80, it gives me an error. I am trying to do the same thing as before:

D1 = B1*(2*1/2*Exp(-((0.5*2/2)+2-1)))

D2 = B1*(2*1/2*Exp(-((1.5*2/2)+2-1))) + B2*(2*1/2*Exp(-((0.5*2/2)+2-1)))

D3 = B1*(2*1/2*Exp(-((2.5*2/2)+2-1))) + B2*(2*1/2*Exp(-((1.5*2/2)+2-1)))+ B3*(2*1/2*Exp(-((0.5*2/2)+2-1)))

D4 = B1*(2*1/2*Exp(-((3.5*2/2)+2-1))) + B2*(2*1/2*Exp(-((2.5*2/2)+2-1))) + B3*(2*1/2*Exp(-((1.5*2/2)+2-1))) + B4*(2*1/2*Exp(-((0.5*2/2)+2-1)))

D5 = B1*(2*1/2*Exp(-((4.5*2/2)+2-1))) + B2*(2*1/2*Exp(-((3.5*2/2)+2-1))) + B3*(2*1/2*Exp(-((2.5*2/2)+2-1))) + B4*(2*1/2*Exp(-((1.5*2/2)+2-1))) + B5*(2*1/2*Exp(-((0.5*2/2)+2-1)))

Any thoughts on why is it not working?

 

[fairwinds]

Probably depends on what you give to "val"

It should be arrays from 1 up to 1-80

I guess formula would be:
=SUMPRODUCT($B$1:B1*(2*1/2*EXP(-(((ROW()-ROW($B$1:B1)+0.5)*2/2)+2-1))))