[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Buy[/TD]
[TD]Product A[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Buy[/TD]
[TD]Product A[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Sell[/TD]
[TD]Product A[/TD]
[TD]1[/TD]
[TD]=(1 x 3)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sell[/TD]
[TD]Product A[/TD]
[TD]2[/TD]
[TD]=(2 x 3)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sell[/TD]
[TD]Product A[/TD]
[TD]4[/TD]
[TD]=(4 x 3)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Buy[/TD]
[TD]Product B[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Sell[/TD]
[TD]Product B[/TD]
[TD]5[/TD]
[TD]=(5 x 7)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Sell[/TD]
[TD]Product B[/TD]
[TD]6[/TD]
[TD]=(6 x 7)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hi Guys, I had tried to resolve the above formula for 2 days and unable to get a workable formula.
What I am trying to achieve is, I wish to fix a formula where I can fix the calculation moving downwards to capture the source at column E. And it can be copy straight away to the next few rows, and the formula will continue to capture what is the next multiply factor on column E.
Example, row D4,D5,D6 are fixed to multiply with the cells at E3
and thereafter the formula can be copied continuously to the bottom of the cells, and row D10, D11 will automatically multiply with the cells at E9
Column A consist of the transaction nature
Column B consist of product name
Column C consist of the cost price
Column D consist of the total costs
Column E is the average of total purchase costs
It seems like only macro can do it, but i would like to keep it simple as possible for the next user to understand how is the whole calculation was derived from. But if simple macro which can helps, i would like to know how to do it as well.
Appreciate if anyone could advise on a workable formula to capture the above scenario.
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Buy[/TD]
[TD]Product A[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Buy[/TD]
[TD]Product A[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Sell[/TD]
[TD]Product A[/TD]
[TD]1[/TD]
[TD]=(1 x 3)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sell[/TD]
[TD]Product A[/TD]
[TD]2[/TD]
[TD]=(2 x 3)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sell[/TD]
[TD]Product A[/TD]
[TD]4[/TD]
[TD]=(4 x 3)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Buy[/TD]
[TD]Product B[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Sell[/TD]
[TD]Product B[/TD]
[TD]5[/TD]
[TD]=(5 x 7)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Sell[/TD]
[TD]Product B[/TD]
[TD]6[/TD]
[TD]=(6 x 7)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hi Guys, I had tried to resolve the above formula for 2 days and unable to get a workable formula.
What I am trying to achieve is, I wish to fix a formula where I can fix the calculation moving downwards to capture the source at column E. And it can be copy straight away to the next few rows, and the formula will continue to capture what is the next multiply factor on column E.
Example, row D4,D5,D6 are fixed to multiply with the cells at E3
and thereafter the formula can be copied continuously to the bottom of the cells, and row D10, D11 will automatically multiply with the cells at E9
Column A consist of the transaction nature
Column B consist of product name
Column C consist of the cost price
Column D consist of the total costs
Column E is the average of total purchase costs
It seems like only macro can do it, but i would like to keep it simple as possible for the next user to understand how is the whole calculation was derived from. But if simple macro which can helps, i would like to know how to do it as well.
Appreciate if anyone could advise on a workable formula to capture the above scenario.