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

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
GarethWhamAsked:
Who is Participating?
 
DaveConnect With a Mentor Commented:
Yep, raja picked up my typo in my paste.

=LOOKUP(1E30,C1:C20)

Cheers

Dave
0
 
sunnycoderCommented:
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
 
rajaloysiousCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
GarethWhamAuthor Commented:
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
 
GarethWhamAuthor Commented:
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
 
rajaloysiousCommented:
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
 
rajaloysiousCommented:
let me know if this doesnt solve your problem.. it seems simple and probably, i am not getting your question right..
0
 
GarethWhamAuthor Commented:
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
 
DaveCommented:
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
 
rajaloysiousConnect With a Mentor Commented:
Fine....This is the solution you were awaiting....
Type this in C30
=LOOKUP(1E20,C1:C29)

This WORKS PERFECTLY FOR ME...

Cheers
0
 
rajaloysiousCommented:
I got this solution from another EE solution...

Have a nice time guys....
0
 
GarethWhamAuthor Commented:
Cheers both,

That works fine. What does the 1E30 part of the formula represent?
0
 
rajaloysiousCommented:
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
 
GarethWhamAuthor Commented:
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
 
gecko_au2003Commented:
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
 
DaveCommented:
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
 
gecko_au2003Commented:
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
 
DaveCommented:
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
 
gecko_au2003Commented:
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
 
gecko_au2003Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.