Hot Topics

Excel: I'm looking for a Formula

  wee eddie 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:???)

  Pine Man 16:06 03 Jan 2020
  wee eddie 18:07 03 Jan 2020

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

  Fruit Bat /\0/\ 19:35 03 Jan 2020

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

  wee eddie 21:36 03 Jan 2020

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

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

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

  wee eddie 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.

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

  wee eddie 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.

What is Markdown?

Markdown lets you add more formatting to your post. Simply type in your post and it will display as written.

If you wish to add bold or italic characters, add a hyperlink to another website, a heading or a horizontal line, simply use the relevent icons above the text input field.

A preview of your post will appear in the grey box below. If you make a change and you're not happy, simply press the back arrow icon to undo.

Post a Reply


Elsewhere on IDG sites

5 video games we want to see as movies

How to draw an eye

The best games on Apple Arcade

Les meilleurs écouteurs et casques audio pas chers (2020)