how can i create a running balance when i have a real big data?

column a date
column b dept
column c doc no.
column d product code
column e product descriptiom
column f issue
column g received
column h balance

if i hv a really big database, how can i calculate the balance of each
product when i key in the received or issue figure?

Please help.

Thank you.

Try this€¦

Copy and paste this formula in I column

If you want to subtract the Received Total from Issue Total then use this
formula
=SUM(G:G)-SUM(F:F)
If you want to subtract the Issue Total from Received Total then use this
formula
=SUM(F:F)-SUM(G:G)

OR

If you want to subtract the each Issue and Received values nearby the cells
then try this in B2

Subtract Received from Issue
=G2-F2
Subtract Issue from Received
=F2-G2
To total the Balance try this
=SUM(H:H)

Copy and paste this formula for the remaining cell.

Hope this will help you.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Eliz" wrote:

how can i create a running balance when i have a real big data?

column a date
column b dept
column c doc no.
column d product code
column e product descriptiom
column f issue
column g received
column h balance

if i hv a really big database, how can i calculate the balance of each
product when i key in the received or issue figure?

Please help.

Thank you.

Is there a formula that i can set that when i key in the product code n
amount of issue/received, it will automatically search for the same product n
calculate the balance for me?

Please let me know.. really desperate abt this.

Thank you.


"Ms-Exl-Learner" wrote:

Try this€¦

Copy and paste this formula in I column

If you want to subtract the Received Total from Issue Total then use this
formula
=SUM(G:G)-SUM(F:F)
If you want to subtract the Issue Total from Received Total then use this
formula
=SUM(F:F)-SUM(G:G)

OR

If you want to subtract the each Issue and Received values nearby the cells
then try this in B2

Subtract Received from Issue
=G2-F2
Subtract Issue from Received
=F2-G2
To total the Balance try this
=SUM(H:H)

Copy and paste this formula for the remaining cell.

Hope this will help you.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Eliz" wrote:

how can i create a running balance when i have a real big data?

column a date
column b dept
column c doc no.
column d product code
column e product descriptiom
column f issue
column g received
column h balance

if i hv a really big database, how can i calculate the balance of each
product when i key in the received or issue figure?

Please help.

Thank you.

Use this formula in H2

=SUMIF(D:D,D2,G:G)-SUMIF(D:D,D2,F:F)

Copy and paste it for the remaining cells of H Column to know the Balance of
Every Products.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Eliz" wrote:

Is there a formula that i can set that when i key in the product code n
amount of issue/received, it will automatically search for the same product n
calculate the balance for me?

Please let me know.. really desperate abt this.

Thank you.


"Ms-Exl-Learner" wrote:

Try this€¦

Copy and paste this formula in I column

If you want to subtract the Received Total from Issue Total then use this
formula
=SUM(G:G)-SUM(F:F)
If you want to subtract the Issue Total from Received Total then use this
formula
=SUM(F:F)-SUM(G:G)

OR

If you want to subtract the each Issue and Received values nearby the cells
then try this in B2

Subtract Received from Issue
=G2-F2
Subtract Issue from Received
=F2-G2
To total the Balance try this
=SUM(H:H)

Copy and paste this formula for the remaining cell.

Hope this will help you.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Eliz" wrote:

how can i create a running balance when i have a real big data?

column a date
column b dept
column c doc no.
column d product code
column e product descriptiom
column f issue
column g received
column h balance

if i hv a really big database, how can i calculate the balance of each
product when i key in the received or issue figure?

Please help.

Thank you.

thanks the formulas works. But i just want to know if instead getting the
full figures in the whole worksheet.. is it posible to get the balance each
transaction instead of the whole lump sum? Please help.

Thank you.

Eliz

"Ms-Exl-Learner" wrote:

Use this formula in H2

=SUMIF(D:D,D2,G:G)-SUMIF(D:D,D2,F:F)

Copy and paste it for the remaining cells of H Column to know the Balance of
Every Products.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Eliz" wrote:

Is there a formula that i can set that when i key in the product code n
amount of issue/received, it will automatically search for the same product n
calculate the balance for me?

Please let me know.. really desperate abt this.

Thank you.


"Ms-Exl-Learner" wrote:

Try this€¦

Copy and paste this formula in I column

If you want to subtract the Received Total from Issue Total then use this
formula
=SUM(G:G)-SUM(F:F)
If you want to subtract the Issue Total from Received Total then use this
formula
=SUM(F:F)-SUM(G:G)

OR

If you want to subtract the each Issue and Received values nearby the cells
then try this in B2

Subtract Received from Issue
=G2-F2
Subtract Issue from Received
=F2-G2
To total the Balance try this
=SUM(H:H)

Copy and paste this formula for the remaining cell.

Hope this will help you.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Eliz" wrote:

how can i create a running balance when i have a real big data?

column a date
column b dept
column c doc no.
column d product code
column e product descriptiom
column f issue
column g received
column h balance

if i hv a really big database, how can i calculate the balance of each
product when i key in the received or issue figure?

Please help.

Thank you.

Copy the below formula and locate the cursor in H2 and paste it.

=SUMIF($D$1:$D2,$D2,$G$1:$G2)-SUMIF($D$1:$D2,$D2,$F$1:$F2)

After pasting the formula in H2 now you can copy the H2 cell and paste it to
the remaining cells.

Keep in mind that you can paste the above formula other than H column also,
but the pasting cell should be the 2nd row. For example if you want to paste
it in J column then you have to copy and paste it in J2 cell only. After
that you should copy the J2 cell and apply it for the remaining cells.

I think I have explained it clearly.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Eliz" wrote:

thanks the formulas works. But i just want to know if instead getting the
full figures in the whole worksheet.. is it posible to get the balance each
transaction instead of the whole lump sum? Please help.

Thank you.

Eliz

"Ms-Exl-Learner" wrote:

Use this formula in H2

=SUMIF(D:D,D2,G:G)-SUMIF(D:D,D2,F:F)

Copy and paste it for the remaining cells of H Column to know the Balance of
Every Products.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Eliz" wrote:

Is there a formula that i can set that when i key in the product code n
amount of issue/received, it will automatically search for the same product n
calculate the balance for me?

Please let me know.. really desperate abt this.

Thank you.


"Ms-Exl-Learner" wrote:

Try this€¦

Copy and paste this formula in I column

If you want to subtract the Received Total from Issue Total then use this
formula
=SUM(G:G)-SUM(F:F)
If you want to subtract the Issue Total from Received Total then use this
formula
=SUM(F:F)-SUM(G:G)

OR

If you want to subtract the each Issue and Received values nearby the cells
then try this in B2

Subtract Received from Issue
=G2-F2
Subtract Issue from Received
=F2-G2
To total the Balance try this
=SUM(H:H)

Copy and paste this formula for the remaining cell.

Hope this will help you.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Eliz" wrote:

how can i create a running balance when i have a real big data?

column a date
column b dept
column c doc no.
column d product code
column e product descriptiom
column f issue
column g received
column h balance

if i hv a really big database, how can i calculate the balance of each
product when i key in the received or issue figure?

Please help.

Thank you.

use pivot table for better results



On Oct 22, 11:20*am, Eliz wrote:
how can i create a running balance when i have a real big data?

column a * * date
column b * * dept
column c * * doc no.
column d * * product code
column e * * product descriptiom
column f * * *issue
column g * * received
column h * * balance

if i hv *a really big database, how can i calculate the balance of each
product when i key in the received or issue figure?

Please help.

Thank you.

i want to try to use pivot tables, but i am really not familiar with it.

"muddan madhu" wrote:

use pivot table for better results



On Oct 22, 11:20 am, Eliz wrote:
how can i create a running balance when i have a real big data?

column a date
column b dept
column c doc no.
column d product code
column e product descriptiom
column f issue
column g received
column h balance

if i hv a really big database, how can i calculate the balance of each
product when i key in the received or issue figure?

Please help.

Thank you.

.