running balance

E

Eliz

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.
 
M

Ms-Exl-Learner

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!
 
E

Eliz

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.
 
M

Ms-Exl-Learner

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!
 
E

Eliz

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
 
M

Ms-Exl-Learner

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!
 
E

Eliz

Thanks. It really help. But i still hv to trouble you with more question.

how do i get the final balance of all the transaction? how do i make it
show the last balance instead of filtering all the transaction?? i would like
to get the summary of the last balance .

Thank you.

Eliz
 
M

Ms-Exl-Learner

Thanks for feeding back and solution for the query you have asked for is
given below.

For Excel 2003
Select the D Column and go to Data>Pivot Table and Pivot Char
Report>Next>Next>Layout
In right of that popup window you can see a Field that is Product Code, Now
click and hold the mouse and drag it to Row. In the same manner do it once
again and take it in Data field also. Now both in Row and Data you can see
the field Product Code. Give ok then click Finish.

Now a new sheet is added and you will see the Product Codes without any
duplication. Just select the product codes and do copy. Now insert a new
sheet and place the cursor in A2 cell and do right click>PASTE SPECIAL>Values.

Now in A1 type Product Code
And in B1 type Issue
And in C1 type Received
And in D1 type Balance

In B2 paste this formula
=SUMIF(Sheet1!$D:$D,$A2,Sheet1!$F:$F)

In C2 paste this formula
=SUMIF(Sheet1!$D:$D,$A2,Sheet1!$G:$G)

In D2 paste this formula
=SUMIF(Sheet1!$D:$D,$A2,Sheet1!$G:$G)-SUMIF(Sheet1!$D:$D,$A2,Sheet1!$F:$F)
Or
=C2-B2

After applying the formula’s in the above cells now copy B2, C2 & D2 cells
and paste it to the remaining cells.

Note that your master data should be in Sheet1 worksheet, if it is not in
sheet1 worksheet then change the sheet name (Sheet1) in the above formula.

For any further clarifications please let us know.

If this post helps, Click Yes!
 
E

Eliz

Will try the pivot table.. but if i use the pivot table, can i still copy teh
last figure to a new sheet for the new mth?

we are actually trying to use the worksheet as stock card. so, we need to
have the final figure at the end to show the begining of the new mth.. Please
advise.

Thank you.

Eliz
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Running Balance in Access 1
Multiple Conditions 2
auto subtract 3
New to Excel: Need help finding functions. Please Help!!! 2
proper formula or function 1
Nested IF 2
Hide blank data 4
Formula for non matching items 3

Top