Forum

Notifications
Clear all

Running Totals

11 Posts
3 Users
0 Reactions
119 Views
(@marvo)
Posts: 7
Active Member
Topic starter
 

Hi All, I was asked to post an example file on utube in answer to a problem with running totals. Basically i would like to add a row when a new transaction takes place but I cant do it, instead I have to put a new transaction at the bottom of the page and then sort each time by date. When I apply the changes suggested online I get the result FALSE. Many thanks.

 
Posted : 05/07/2022 2:09 am
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

Hello,

The file seems broken, I can’t download it.

Go to the blog section and search for running, you will get some articles to read. Here is one.
https://www.myonlinetraininghub.com/excel-table-running-total-formula

Br,
Anders

 
Posted : 05/07/2022 7:22 pm
(@marvo)
Posts: 7
Active Member
Topic starter
 

I'll try again.

 
Posted : 06/07/2022 1:29 am
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

Hello,

I have some questions.

Where do you want to add a row?
What is the issue when adding the row where you want it?
What was suggested that does not work?
Why are you not using an Excel table for your data?

Br,
Anders

 
Posted : 06/07/2022 4:07 pm
(@marvo)
Posts: 7
Active Member
Topic starter
 

Hi Anders, I've now tried to make it work by creating a table but I've no experience with this.

For example, Can you tell me why when you add a row to this table, say ROW 183, column F copies down automatically, column G doesn't. I can't spot a difference in the formula?

Also what is the relevance of =6 at the start of the formula? It seems to work whatever number you put it.

Many thanks.

 
Posted : 07/07/2022 1:12 am
(@marvo)
Posts: 7
Active Member
Topic starter
 

I'm all good now. Thank you.

 
Posted : 07/07/2022 11:53 am
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

Hello,

Issues like the one you have with column G not copying the formula down is often related to some error/issue on the worksheet or in rare cases, the workbook. If you for example copy the first 10 or 20 rows and paste it in on a new worksheet or workbook and make it a table, you will see it will work as intended.

If it wasn't you that added the =IF(ROW()=6... in the formula then I don't know who did.

Apart from these issues, weren't there others you wanted help with?

Br,
Anders

 
Posted : 07/07/2022 4:17 pm
(@marvo)
Posts: 7
Active Member
Topic starter
 

I basically started again, got rid of the table, then created another. It works now (sort of) but it always seems with excel that when you resolve one issue another emerges. I now find I cannot "sort" the data by date without all the references going haywire. However now I can add and delete a row that may not be necessary.
I enjoy these challenges, I'm sure they can all be solved, its just a slow learning process.

 
Posted : 08/07/2022 1:49 am
(@marvo)
Posts: 7
Active Member
Topic starter
 

I've got so far but have again come up against a brick wall. In the example workbook sheet 2 works okay until you add a row/transaction, then the formula no longer automatically copies down correctly. I've shown an example of what happens on sheet 3 by date 13th July. Many thanks.

 
Posted : 13/07/2022 11:37 am
Riny van Eekelen
(@riny)
Posts: 1201
Member Moderator
 

Hi,

I took a close look at your file and concluded that you violated almost every rule in the book when it comes to structuring data and writing consistent formulas. Therefore, I have added another sheet that does work as you intend it. All conditional formatting, colours and borders have been removed and ALL data is now part of a structured table, not just the last few columns.

Now, I should have used structured table references as well but that would have changed the look of the formulas completely. Please see for yourself if I've understood you correctly..

 
Posted : 14/07/2022 1:44 am
(@marvo)
Posts: 7
Active Member
Topic starter
 

Thank you. At first inspection that seems to work perfectly, exactly what I want. The first row didn't work but I just added an extra row at the top with the starting figure and now it does. I'm extremely grateful. Laugh

 
Posted : 14/07/2022 2:13 am
Share: