Hot Topics

We regret to inform you that the PC Advisor Forums are now closed, and you are unable to post new comments or conversations. If you have a question about the forums, please contact us.

Excel: I'm looking for a Formula

  Anon-281249 16:00 03 Jan 2020

I know that it must be simple, but I just can't remember, and I'll probably kick myself when someone points it out!

To add up the total of numbers in a Columns is SUM (A1:A100)

However that Column keeps growing and I want the figure at the top to reflect this, without having to be constantly updating the End Point. I have already Frozen the necessary Panes.

So the new Formula for the Column, should read, SUM (A1:???)

  Anon-2432433 16:06 03 Jan 2020
  Anon-281249 18:07 03 Jan 2020

Sorry, as far as I can understand, that answer only works for Google Docs

  Anon-319210 19:35 03 Jan 2020

Cant you just put a massive nimber as your second number? e.g. A1:A1000

  Anon-281249 21:36 03 Jan 2020

That's roughly what I decided to do. One suggestion was a number in the millions.

  Anon-286468 15:46 04 Jan 2020

wee eddie

what you could do, though not ideal, is add a column at the side of each column you are totalling. In A1, some text, eg:- "Total of this Column." In B1 the formula =SUM(a:a). Everytime you add something to an 'a' cell b1 will give you a running total of all 'a' cells. Likewise for the rest of your columns.

  Anon-2416588 20:47 04 Jan 2020

I'm not sure if this is exactly what you want but give it a try and see.

Say you have 100 rows in the A column with data. Put =SUM(A1:A104) in cell A105, so you have the sum you want in cell A105 and 4 unoccupied cells above. The column then grows by adding data to cell A101, A102, etc, but each time you occupy a free cell, you insert a new row.

But you want to have the total at the top, so you need to insert a row above A1 and in the new now unoccupied cell A1 you need to put =A? where A? is the current location of the sum you created initially.

I've got a few set up like this and they work fine.

  Anon-281249 23:12 04 Jan 2020

qwbos: I have frozen Rows 1 to 3. Row 2 contains the Formulas for Running Totals and Balances.

Thereafter: the number of Rows grows day by day.

Microsoft recommends that I use the formula =Sum(A4:A1,048,576) but, on consideration, I shall go with =Sum(A4:A1000) as has been suggested.

I was hoping to find a Symbol, which would save me the need to define the Range ahead of the end of the year, which is just short of 12 months away.

  Anon-2416588 00:14 05 Jan 2020

Am I right in assuming you're entering new data manually? If not, you'd need to associate each entry with an input date then you could easily truncate it at the end of the year. But it's messy and cumbersome.

If you're creating something that's continuous over a number of years, you could create individual yearly worksheets within a workbook. It's not too difficult to then link data between worksheets if you need to.

  Anon-281249 08:48 05 Jan 2020

qwbos: This is my first year of living on a "fixed" income and, while estimates of expenditure are all very well and good, an accurate picture of my annual spending patterns will help guide me in the future.

At the present, I do not expect to run beyond a single year's expenditure figures as it really is something of a waste of time. However I shall review the situation in December 2020 and if the year's spending is within a gnats-whisker of my estimate (budget), I shall not bother with a repeat performance.

I am most interested in finding out my actual spending down at the Ice Rink, while Curling. I had a mental budget of about £2000.00 per season, which includes "hospitality!" and, so far, that appears to be pretty close to reality.

1x1 pixel
Elsewhere on IDG sites

£129 Roku Streambar Aims to Complete the Home Entertainment Experience

Apple iMac 2020 review

No, iOS 14 widgets are not spying on your iPhone

Google Pixel 5 : date de sortie, prix et autres rumeurs