Solved

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

Posted on 2004-09-20
21
243 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
  • 6
  • 5
  • 5
  • +2
21 Comments
 
LVL 45

Expert Comment

by:sunnycoder
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:GarethWham
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yep, raja picked up my typo in my paste.

=LOOKUP(1E30,C1:C20)

Cheers

Dave
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 8

Expert Comment

by:rajaloysious
Comment Utility
I got this solution from another EE solution...

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

Expert Comment

by:Dave Brett
Comment Utility
0
 

Author Comment

by:GarethWham
Comment Utility
Cheers both,

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

Expert Comment

by:rajaloysious
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
squareUp  challenge 22 104
wefewf 2 42
WPF - External data binding to controls inside a UserControl 2 43
wordlen challenge 3 46
Purpose To explain how to place a textual stamp on a PDF document.  This is commonly referred to as an annotation, or possibly a watermark, but a watermark is generally different in that it is somewhat translucent.  Watermark’s may be text or graph…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now