Solved

How can I display a running value at the bottom of a sheet in Excel

Posted on 2004-09-20
21
248 Views
Last Modified: 2010-04-17
In my excel sheet I have a column called running balance. I want to know how to take the value in the running balance column and display it in a cell at the bottom of the sheet.

The value in the running balance will obviously change as a new entry is made on the sheet. Can anyone help?

Regards,

Gareth
0
Comment
Question by:GarethWham
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 5
  • +2
21 Comments
 
LVL 45

Expert Comment

by:sunnycoder
ID: 12100469
Hi GarethWham,

Method is provided in excel help

Set up a worksheet like the following example, or copy it to a blank worksheet.

How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.
 

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
Example
           A                      B                 C
1      Deposits Withdrawals Balance
2      $1,000          $625        =SUM(A2,-B2)
3       1000             740           =SUM(C2,A3,-B3)
 
Click anywhere outside cell C3 to see the calculated total.
You can change values in A2 A3 etc to verify

Sunnycoder
0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 12100580
Very Simple...
I think you want to show the summ of a particular column(say H) in the bottom of the sheet say A500
Go to A500, then type   =SUM(H2,H400)
That is it...

If you want to show the value of a particular cell (say H400) in another cell say A500
Then Go to A500 and stype    =H400

Hope this solves your problem...
Cheers
0
 

Author Comment

by:GarethWham
ID: 12100618
Hello,

I know how to obtain the value for the running balance but dont know how to display the constantly changing value in a seperate cell for example:

                     A                    B                  C
                   In                   Out              Balance
1               £1000              £100              £900
2                 £900              £100              £800
3                 £800              £700              £100

So, when the value is obtained I want to display it in a cell at the bottom of the sheet:
.
                                                              C
Balance                                                 £100

The value is going to change everytime an entry is made there could be several entries on a sheet

So basically I want to display the balance  in the 'C' Column at the bottom of the sheet. I am doing this so I can easily transfer the value to another sheet.

Thanks , regards

Gareth
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GarethWham
ID: 12100633
rajaloysious,

I also know how to do this. The value is going to constantly change. So everytime an entry is made it takes the new value from the column and displays it in the neccessary cell. See Above.

Thanks Gareth
0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 12100638
you go to the particulat cell at the bottom (say A30)and type in this and that is it..

=C3

Whenever the value changes in C3, the cell in the bottom will change as well..

Cheers
0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 12100648
let me know if this doesnt solve your problem.. it seems simple and probably, i am not getting your question right..
0
 

Author Comment

by:GarethWham
ID: 12100733
Ok I will try to explain it further.

As I make an entry on the sheet I will be utilising a row each time.

So say I want to diaply a value in C30.

I want to take the changing running balance and display it in C30

I want to set it up automatically.

So say I have got,

                                       C
                             Running balance
1                                     500
2
3
4
5

As it stands I want to display 500 in C30
Now I make a new entry:

                          C
                             Running balance
1                                     500
2                                     300
3
4
5

So, now I want to display 300 in C30

I want to set it up so that when a new entry is made cell C30 is automatically updated.
I hope this explaind the question further,

Regards,

Gareth
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 12100874
Hi Gareth

To return the last used value in C1:C20 then in C30
=LOOKUP(1E30,1:C20)

In future you may want to post your Excel questions in the Excel TA

Cheers

Dave
0
 
LVL 8

Assisted Solution

by:rajaloysious
rajaloysious earned 50 total points
ID: 12100918
Fine....This is the solution you were awaiting....
Type this in C30
=LOOKUP(1E20,C1:C29)

This WORKS PERFECTLY FOR ME...

Cheers
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 450 total points
ID: 12100952
Yep, raja picked up my typo in my paste.

=LOOKUP(1E30,C1:C20)

Cheers

Dave
0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 12100986
I got this solution from another EE solution...

Have a nice time guys....
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 12101004
0
 

Author Comment

by:GarethWham
ID: 12101198
Cheers both,

That works fine. What does the 1E30 part of the formula represent?
0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 12101269
it is a large number to match.
if it doesnt match, it doesnt return a error, but will return the last filled value..

That is exactly your requirement
0
 

Author Comment

by:GarethWham
ID: 12102879
Ok,

Just one more query. However when I use the formula and then try to use SUM to calculate the actual running balance as normal the formula automatically display a zero value, fine. But I want to input the formula all the way down the sheet. Like this:


                            A                       B                       C
                           In                     Out                   Balance
1                     £1000                  £100                    £900 - SUM(A1-B1)
2
3
4

So, I want to copy the formula down the sheet but when I do it produces "0"

As shown below:

           A                       B                       C
                           In                     Out                   Balance
1                     £1000                  £100                    £900 - SUM(A1-B1)
2                                                                           £0.00
3                                                                           £0.00
4                                                                           £0.00

Is there a way to stop the formula producing the default 0?

Otherwise I end up getting a '0' in the cell that I required the formula for earlier.

Thanks

Gareth


0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 12106398
hi,

my email address is in my profile ! Any chance you can email me the worksheet and I will have a look at what you are trying to do exactly.

You could put values in A1 to A# or what ever the last entry is and then in cell C30 you put the function =sum(A1:A#) or what ever the last entry is.

so you have data in column A from row 1 to row 100 then the function would be :

=sum(A1:A100)

The above function would go in C30 as that is where you wanted it to be updated as a running total.

If that is not what you want then like i said above, email me it and explain further.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 12107688
Gareth

In C1 use
=IF(OR(A1="",B1=""),"",A1-B1)

and copy down to C20 (or your endpoint)
So if there is a blank in either the A or B column then the C column returns 0. As the LOOKUP in C30, ie
=LOOKUP(1E30,C1:C20)
matches only numeric values, your running total will return your last numeric value and it skips the ""

gecko_au2003, you may want to revist the membership agreement with regards to the rules on using email.

Cheers

Dave


0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 12108725
Dave,

take a look here:

http://www.experts-exchange.com/Community_Support/Q_21102300.html

I already asked about staying in touch with other members ;) :P

If it is against the rules then if an admin would please let me know because I dont wanna be doing nothing to get my ass banned :)
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 12108769
Hi gecko_au2003 ,

I'm yet to see anyone get banned for this, most of us make the mistake of trying to make direct contact once and get pulled up by someone else .... :)

My email address is in my profile too, but I ask members to use it as a last resort only if they can't solve their problem directly in the forum AND if they have no access to host their problem on the web.

If thats the case then I'm happy for them email it to me and I'll put the file on the web for them so that all the experts can have a crack at the question. If another expert has done the bulk of the preliminary work then I'll stand aside from the question so that there isn't a perception that I have unfairly gained an advantage

Cheers

Dave
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 12108788
I just dont like going to websites because people have gotten my ip by doing that because this computer is a shared computer, I am not saying I went to the site.

Just I would much rather they emailed me it as apposed to tricking me in to **** like that.

I have a router and a firewall so I dont mind too much but I would much rather not many people had my ip if i can avoid it , ya know.
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 12108806
As far as answering a question is concerned, my point of view is people like to get the simplest answer as fast as possible. Not that I am trying to hog anything, just I look around and if I find a question I can answer then I do and if I get it wrong then I learn something.

So to me its a win win situation.

:D
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question