Cells Multiplication offsetting and "resetting" each time

[ranashsa]

Hi everyone !

For my first post, i thought of asking a stupid question. . Here we go :

I have excel file a bit like that but with random numbers going from 1 to 500000

[TABLE="width: 500"]
<tbody>[TR]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]40[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD]360[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



The cells in row two are multiplied with the cells in row 1 beginning with the first cell in row and added to the value of the multiplication of the previous cells "offset by 1 to the right each time

---

To give you an idea of the calculation

1* 10 = 10 1st column
(1*20) + (2 *10) = second column
(1*30)+ (2*20) + (3 *10) = third column
(1*40) +(2*30)+ (3*20) + (4*10) = 200 fourth and so on


The problem with using a formula is that i have to manually add some part of the formula each time
For example, in the third column , i was able to generate the first two terms using "offset"but i still need to manually enter the third term...

Now i have turned to writing a vba code but cannot find a way to do it...(i'm quite new with it and i'm a bit bad with using loops)

Can someone give me a push in the right direction?

Cheers
Vjiranashsa

 

[XOR LX]

Hi.

I'm confused. You mention only offsetting to the right, yet aren't some of the products formed by offsetting to the left?

For example, your last example:

(1*40) +(2*30)+ (3*20) + (4*10)

does not seem to obey your rule that:

"The cells in row two are multiplied with the cells in row 1 beginning with the first cell in row and added to the value of the multiplication of the previous cells "offset by 1 to the right each time."

Can you clarify?

Regards

 

[Marcelo Branco]

Not sure if i understand what you need.

Maybe...


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

10​

[/td][td]

20​

[/td][td]

30​

[/td][td]

40​

[/td][td]

50​

[/td][/tr]


[tr][td]

2​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][/tr]


[tr][td]

3​

[/td][td]

10​

[/td][td]

40​

[/td][td]

100​

[/td][td]

200​

[/td][td]

350​

[/td][/tr]
[/table]

Formula in A3 copied across
=SUMPRODUCT($A2:A2,N(OFFSET($A$1,,COLUMNS($A3:A3)-(COLUMN($A$1:A1)-COLUMN($A$1)+1))))

Hope this helps

M.

 

[jasonb75]

No need for vba, a formula based on this example should work without needing to manually change it each time.

=SUMPRODUCT($A1:A1,N(OFFSET($A2,0,COLUMN(A1)-COLUMN($A1:A1))))

 

[ranashsa]

Hi.

I'm confused. You mention only offsetting to the right, yet aren't some of the products formed by offsetting to the left?

For example, your last example:

(1*40) +(2*30)+ (3*20) + (4*10)

does not seem to obey your rule that:

"The cells in row two are multiplied with the cells in row 1 beginning with the first cell in row and added to the value of the multiplication of the previous cells "offset by 1 to the right each time."

Can you clarify?

Regards

hi thanks for taking the time to look at my problem ,

to answer your question what i meant is that each time a new cell is reached, the multiplication starts over from the first cell first row again. then it offsets to the right. hope i made it clearer

 

[ranashsa]

Not sure if i understand what you need.

Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

10​

[/TD]
[TD]

20​

[/TD]
[TD]

30​

[/TD]
[TD]

40​

[/TD]
[TD]

50​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

10​

[/TD]
[TD]

40​

[/TD]
[TD]

100​

[/TD]
[TD]

200​

[/TD]
[TD]

350​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in A3 copied across
=SUMPRODUCT($A2:A2,N(OFFSET($A$1,,COLUMNS($A3:A3)-(COLUMN($A$1:A1)-COLUMN($A$1)+1))))

Hope this helps

M.

Thanks!!! i will try that and get back to you

 

[ranashsa]

No need for vba, a formula based on this example should work without needing to manually change it each time.

=SUMPRODUCT($A1:A1,N(OFFSET($A2,0,COLUMN(A1)-COLUMN($A1:A1))))

thanks jacob, i'll try that

 

[ranashsa]

ahem .... i get value each time.can you explain what the function does( sorry... i'm having a bit of trouble with it)

 

[jasonb75]

Did you make any changes to the ranges (cells) used in the formula in order to make it fit your real data?

Please post your edited version so we can see where you went wrong.