I was wondering how I can keep a running sum of a currency column in my excell sheet. Here is an example.
The problem is, is that I am inporting the data from a SQL Server table and the data needs to be refreshed regularly. After a refresh there will be lines added to the Excel sheet and therefore overrun the sum. Can I make it so the sum cell moves down as need? I will also need to keep a running average. Is there some way to do this? In other words, I might have 50 rows added up, then refresh the table and there will be 55 rows. I will now need to take these new rows into the average.
The easiest way is to use a defined dynamic range for the data, then put your summary on another sheet.
Let's say your data is on a sheet labeled Import, and column K has the totals. Go to INSERT > NAME > DEFINE. In the dialog box, give it a name, i.e. KTotal. In the Refers to box at the bottom put in this formula:
Click ADD, then OK.
Now on another sheet, put in this formula into cell A1:
Now, when you refresh your data, the named range will appropriately change, and so the sum formula will reflect that change.
old, slow, and confused
but at least I'm inconsistent!
Thanks for your help. That's a little too involved for my needs. I worked it out. I just inserted some blank lines at the top of my sheet and made my sum and average formulas there. Thanks for your time.