1) A thorough explanation of how the Row(s) & Columns(s) functions work. They can be confusing but they're essential. 2) The hide-zero tip is quite useful and makes a big difference in creating reports. 3) When I click "Click here to read in your browser" I get your "Table Running Total" page. Not a problem but I thought I'd mention it. 4) Thanks for all-your lessons are always helpful.
Great explanation on running totals! One problem with the 3rd formula is slowness, when data is huge; The 1st formula is about speed, a modified version can solve the insert/delete row problem, = N(OFFSET([@RunningTotal], -1, 0)) + [@Values] You can avoid using OFFSET by creating a Named Range "CellAbove" and set it reference one cell above. becoming = N(CellAbove) + [@Value]
I agree that this is the preferred solution for large data sets. I tend to use something like 'priorTotal' rather than the more generic 'CellAbove' but it is the same thing, The extra trick is that the formula writer has to understand the behaviour of Names that refer to relative references. Your use of the function N() also gets over the problem of the error created by adding the header row to the first data entry rather neatly. I used SUM to add two numbers as an alternative implementation = SUM(priorTotal, [@Values])
This was a lifesaver - I had the problem with using subtotal due to wanting to keep a running total even if I filtered the date but the logic was the same. Thanks so much😀
Thank you!! I always enjoy your tutorials. I am a subscribed member of you channel and it has been the best thing I've done!! I have learned so much from you and your videos. Thank you!!
I like those methods. As an alternative, try this simple one for your data in the Running Total field: =N(OFFSET([@[Running Total]],-1,))+[@Values] It works great for chequebooks, where you have Withdrawals, Deposits and Balance as field names. =N(OFFSET([@Balance],-1,))+[@Deposits]-[@Withdrawals]
Hi Mynda, another way, using dynamic arrays, and lambdas ar : any array, nonnumeric values will be considered 0’s k : integer, 0 or ignored, running total all the way down; k > 0, every k rows; k < 0 (-k), every other k-th row ARUNTOT = LAMBDA(ar, k, LET( n, ISNUMBER(ar), a, IF(n, ar, 0), r, ROWS(a), x, IF(k, MIN(INT(ABS(k)), r), r), c, COLUMNS(a), s, SEQUENCE(r), q, QUOTIENT(s - 1, x) + 1, m, MOD(s - 1, x) + 1, y, IF(s >= TRANSPOSE(s), IF(k >= 0, --(q = TRANSPOSE(q)), --(m = TRANSPOSE(m))), 0), MMULT(y, a) ) );
Thanks so much! Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
oh, that explains why I was getting errors with formulas used in a table; it wants to have the same formula in each row, and I must use the table structure reference to calculate the values. Can you elaborate in a separate video on this matter? As always, I love your videos.
Not sure what you mean. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Nice cool examples for running totals. You can also use the OFFSET FUNCTION inside the SUM Function to return expanding cell reference where the SUM aggregates the values within the range. =SUM($E$5:OFFSET($E$4,COUNT($E$5:E5),,,))
Tip. Your video takes up a full screen, which is fine except on a mobile, some of the screen estate is taken up with a hole for a camera lens and some of the detail is not viable. Thank you 👍
Mynda. Good material as always! You will need to add a dynamic array option sooner or later though. = SCAN(bFwd, Values, Addλ) where Addλ refers to =LAMBDA(u,v,u+v)
Many thanks Super useful, but can you please advise if i want to add a division componet how the formula will run? To illustrate, we have 3 columns, A, B, and C. Column C includes average cost, so in first row, i divide cost (A)over units to (B)come up with average cost per unit (C). In Row 2, i want the running total of Column (A)/ Cumulative total of Column (B), to come up wit new average in Column C, can you kindly advise how to do it ? Many thanks in advance
Nice, Mynda! Thank you! Quick Question, you are typing your formula in D4 and you are getting the values, but how come your formulas are showing in E4 and downward as well? I clicked on Show Formulas and it replaced my values and showed Formulas. I want to see Formulas in a separate column. Thanks
Another excellent tutorial - Quick question though - I import 3 bank accounts in CSV format into one excel table to keep tabs on my budget - Running totals are included in all 3 CSV downloads - I need to be able to extract the latest running total for each account as I update the table each month , I then use these 3 figures to update my dashboard - In words I need to search the table using the account number for each account and ignore all running totals except the very latest running total and use it to update my dashboard. Probably several ways to do this but I have not been successful so far. Any ideas welcome - thanks in advance.
You could use INDEX & MATCH to find the last date for each account and then return the corresponding balance: www.myonlinetraininghub.com/find-the-last-value-in-a-column
Can Sumif be used to have an empty cell if there is no value. So lets say we want running value stops when there are not further values to add. I hope I made myself clear
Possibly, but there might be a better way. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hello. As usual, precise presentation of the problem. You are The Professional. By the way, I wanted to ask if there are any advantages / disadvantages to using this formula: [ =SUM(Table[[#Headers];[Values]]:[@Values]) ] with Your sum and index?
Hi , can we get the running total of calculated field, e.g I have a weekly data set of target vs actual, I want to calculate running achievement %(achievement for week 1+ week 2/target for week 1+ week 2 and so on)
In theory, yes. If you get stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
The error means you have missed one of the components of the formula. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
You can use a PivotTable to do this. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
my fault! I tried in SUMIFS, it worked in table. Meanwhile, you use 1 in index to refer first column reference. I tried to use Rows to refer last one.Also I found Index is useful than OFFSET since I can use to get eg. last 5 records to make forecast.@@MyOnlineTrainingHub
I would appreciate it if you could help me with the running balance for this data that i have in EXCEL TABLE. I have an opening balance on January 1st of RS. +10,000, received cash on January 5, Rs +20,000, and paid cash on January 8, Rs -4,000. I'm trying to figure out a formula for my data that i have in EXCEL TABLE to show the running balance after each transaction.
I have this exact problem when adding rows within a finance sheet with money going in or out, however I don't know how to add your alteration. My formula is: =IF(C6="VIRGIN",L5+F6,IF(D6="VIRGIN",L5-F6,L5)) The L5+F6 or L5-F6 is I'm guessing where the SUM has to go so I tried =IF(C6="VIRGIN",SUM($L$5+F6,IF(D6="VIRGIN",SUM($L$5-F6,L5)))) but it returned FALSE. Can you see where I'm going wrong or doesn't your way apply here? ("F" is money coming in or going out, "L" is the running total, C&D identify the source of the transaction)
@@MyOnlineTrainingHubThank you for your reply. Sadly after making the changes I'm still getting the result FALSE. Not to worry. I've been adding rows at the bottom (for many years) and then sorting them by date. I can continue to do that but it would have been nice to have added the rows on the appropriate day. Thanks for trying, it's appreciated.
I've worked out why it isn't working. Its when "VIRGIN" isn't in either C6 or D6. In my original formula it reverts to L5 but not when you make the changes.
I got rid of the FALSE return by changing the formula to =IF(C6="VIRGIN",SUM($L$5+F6),IF(D6="VIRGIN",SUM($L$5-F6),L5)) However it no longer works correctly, in ROW 33 it takes money from L5 when it should deducting money from the running total in L32. Oh well.
Hi Mynda, In sheet Non-tables we can use this formula (cell ranges is not hard keyed): =IF(E5:E20="","",BYROW(E5:E20,LAMBDA(a, SUM(E5:a)))) In sheet Excel Tables we can use this formula: =IF(Table1[Values]="","",BYROW(Table1[Values],LAMBDA(a, SUM(Table1[@Values]:a)))) when 'a' is a variable. Same formulas for MAP function, just to change BYROW with MAP. Best regards, IlirU Note: Regardless of these formulas I gave above, I think it was an excellent tutorial (as always... your tutorials are excellent). Thank you!
I would suggest reconsidering the issues that led to your conclusion about tables. I would recommend them strongly for source data, even to the point of regarding any values held outside a table as an error. The gaudy stripes and filter buttons are largely unnecessary but structured references that adjust as the date changes make the workbook more intelligible and more robust. By all means move away from the table for calculation since array formulas conflict with table structure.
@@peterbartholomew7409 hi, hoping I might get a response from this question. I get paid every week so I'm looking for an app or excel formula that will look at all my income and the dates I get paid, then look at all my bills per month and plot out where to pay them during the month (example.. I have $100, bill is $120, so then it automatically figures out how to spread that out). I figure a running balance will help with this. I'm guessing I'll need some if, then statements and I'll watch this video.
Fantastic, I have a table with running totals, you have no idea how long I've been struggling with this!
So glad it helped, Karen!
1) A thorough explanation of how the Row(s) & Columns(s) functions work. They can be confusing but they're essential. 2) The hide-zero tip is quite useful and makes a big difference in creating reports. 3) When I click "Click here to read in your browser" I get your "Table Running Total" page. Not a problem but I thought I'd mention it. 4) Thanks for all-your lessons are always helpful.
Thanks so much! Sorry about the link. My bad. You'll find the correct one in the video description.
Great explanation on running totals! One problem with the 3rd formula is slowness, when data is huge;
The 1st formula is about speed, a modified version can solve the insert/delete row problem, = N(OFFSET([@RunningTotal], -1, 0)) + [@Values]
You can avoid using OFFSET by creating a Named Range "CellAbove" and set it reference one cell above. becoming = N(CellAbove) + [@Value]
I agree that this is the preferred solution for large data sets. I tend to use something like 'priorTotal' rather than the more generic 'CellAbove' but it is the same thing, The extra trick is that the formula writer has to understand the behaviour of Names that refer to relative references. Your use of the function N() also gets over the problem of the error created by adding the header row to the first data entry rather neatly. I used SUM to add two numbers as an alternative implementation
= SUM(priorTotal, [@Values])
Thank you for your valuable explanation .
Glad it was helpful!
This was a lifesaver - I had the problem with using subtotal due to wanting to keep a running total even if I filtered the date but the logic was the same. Thanks so much😀
Glad it helped!
You are awesome, seems all my running totals issues will say goodbye from today, thanks a lot for making our lives easy.
Happy to help!
Very useful ... the second of the three methods shown (non Excel table) is elegantly simple. Thank you ... subscribed
Awesome, thank you!
Thank you!! I always enjoy your tutorials. I am a subscribed member of you channel and it has been the best thing I've done!! I have learned so much from you and your videos. Thank you!!
Awesome! Thank you!
That's super helpful and definitely something I can see myself using.
Cheers!
Great to hear!
I like those methods. As an alternative, try this simple one for your data in the Running Total field: =N(OFFSET([@[Running Total]],-1,))+[@Values]
It works great for chequebooks, where you have Withdrawals, Deposits and Balance as field names. =N(OFFSET([@Balance],-1,))+[@Deposits]-[@Withdrawals]
Thanks for sharing!
Great solution. Thanks for sharing
Hi, I'm trying to build my own spreadsheet for paying bills right now. What does this formula do, exactly?
I always wondered why my tables didn't update properly in the running total columns; I'm now going to fix them all. Many thanks!!
My pleasure, Paul 😊
Hello, you do an outstanding job with your examples and explanations. The topics are relevant and useful!
That’s great to hear 🙏
Thanks Mynda for these ideas. Liked the one in table with INDEX to handle deletion/insertion, really cool!
Great to hear, Vijay 🙏
Mynda you’re a genius you that don’t you??! Thank you so much 😊
Thanks so much!
Thanks Mynda, the table tip was great!
Glad you liked it!!
I was always missing this one. Thanks ❤️
Glad it was helpful, Andre!
Thank you so much! very much needed table referencing tips
You're very welcome!
and, we can use pivot tables and PQ for running totals :)
Thank you Mynda for this helpful tutorial
You're very welcome, Emre!
Thanks. Instead of Index table reference can be used as SUM(Table1[[#Headers],[Values]]:[@Values])
Yes, others have also made this suggestion. Thanks for sharing 😊
@@MyOnlineTrainingHub , Correct I did not go thro' all the comments. sorry for the noise
No worries at all 😊
This was very helpful. Thank you.
You're welcome! 😊
Hi Mynda, another way, using dynamic arrays, and lambdas
ar : any array, nonnumeric values will be considered 0’s
k : integer, 0 or ignored, running total all the way down; k > 0, every k rows; k < 0 (-k), every other k-th row
ARUNTOT = LAMBDA(ar, k,
LET(
n, ISNUMBER(ar),
a, IF(n, ar, 0),
r, ROWS(a),
x, IF(k, MIN(INT(ABS(k)), r), r),
c, COLUMNS(a),
s, SEQUENCE(r),
q, QUOTIENT(s - 1, x) + 1,
m, MOD(s - 1, x) + 1,
y, IF(s >= TRANSPOSE(s), IF(k >= 0, --(q = TRANSPOSE(q)), --(m = TRANSPOSE(m))), 0),
MMULT(y, a)
)
);
🤯
Omg what does this do???
I always learned something here and I love it thank you very much
Great to hear 😊
Fantastic explanation. Thank you. Would you be able to show how to do a running total (stock on hand, i.e stock in and out) by product?
Thanks so much! Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Very effective tips and thanks for sharing
My pleasure 😊
Clever…thank you. These are great tips.
Glad you like them, Jeff!
Great solution for tables. Thanks for sharing
Glad you like them!
Hi Mynda!Really Helpful Tips...Thank You :)
Great to hear, Darryl!
Awesome tip especially for the Excel tables
Glad you think so!
Gracias...saludos desde México.
My pleasure 😊
How about 4th way =SUM($B$4:[@Value]) ? It does not break table formula and I does not need the extra 'index' function :)
Thanks for sharing, Tomasz.
oh, that explains why I was getting errors with formulas used in a table; it wants to have the same formula in each row, and I must use the table structure reference to calculate the values. Can you elaborate in a separate video on this matter? As always, I love your videos.
Yes, correct, Hector. There's a tutorial on Excel Tables here: th-cam.com/video/Du73CPqWGQw/w-d-xo.html
Hey. Thanks for the video.
How can we run totals in excel table, partition by specific column?
Not sure what you mean. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thanks for sharing useful contents.
My pleasure 😊
Nice cool examples for running totals. You can also use the OFFSET FUNCTION inside the SUM Function to return expanding cell reference where the SUM aggregates the values within the range.
=SUM($E$5:OFFSET($E$4,COUNT($E$5:E5),,,))
Yes, OFFSET does the same as INDEX in this case. Except, OFFSET is volatile, so not ideal to insert in hundreds/thousands of times in a column.
@@MyOnlineTrainingHubMyOnlineTrainingHub Thanks for highlighting its volatility 👍👍
Tip. Your video takes up a full screen, which is fine except on a mobile, some of the screen estate is taken up with a hole for a camera lens and some of the detail is not viable.
Thank you 👍
Thanks for the feedback, Andrew.
Excellent.
Many thanks!
Thank you!!!
You're welcome!
Mynda. Good material as always! You will need to add a dynamic array option sooner or later though.
= SCAN(bFwd, Values, Addλ)
where Addλ refers to
=LAMBDA(u,v,u+v)
Cheers, Peter 😊 nice LAMBDA 🙏
Kindly zoom data cells because it hard to read in mobile screen
My current videos zoom 😉
Many thanks Super useful, but can you please advise if i want to add a division componet how the formula will run? To illustrate, we have 3 columns, A, B, and C. Column C includes average cost, so in first row, i divide cost (A)over units to (B)come up with average cost per unit (C). In Row 2, i want the running total of Column (A)/ Cumulative total of Column (B), to come up wit new average in Column C, can you kindly advise how to do it ? Many thanks in advance
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Brilliant!! Thanks.
My pleasure 😊
Nice, Mynda! Thank you! Quick Question, you are typing your formula in D4 and you are getting the values, but how come your formulas are showing in E4 and downward as well? I clicked on Show Formulas and it replaced my values and showed Formulas. I want to see Formulas in a separate column. Thanks
I used the FORMULATEXT function to display the formulas.
@@MyOnlineTrainingHub Thank You so much!
I think using SCAN & LAMBDA is a better option to get running totals
😁 says a few users on the Excel beta version!
@@MyOnlineTrainingHub I do not know if sequence is still in beta
Single cell formula :
=LET(a,E5:E12,r,ROWS(a),s,SEQUENCE(r),MMULT(IF(SEQUENCE(,r)
Another excellent tutorial - Quick question though - I import 3 bank accounts in CSV format into one excel table to keep tabs on my budget - Running totals are included in all 3 CSV downloads - I need to be able to extract the latest running total for each account as I update the table each month , I then use these 3 figures to update my dashboard - In words I need to search the table using the account number for each account and ignore all running totals except the very latest running total and use it to update my dashboard. Probably several ways to do this but I have not been successful so far. Any ideas welcome - thanks in advance.
You could use INDEX & MATCH to find the last date for each account and then return the corresponding balance: www.myonlinetraininghub.com/find-the-last-value-in-a-column
@@MyOnlineTrainingHub I solved it using =INDEX('Raw Data'!H:H, MAX(IF(('Raw Data'!D:D=XXXXXXX)*('Raw Data'!A:A
Can Sumif be used to have an empty cell if there is no value. So lets say we want running value stops when there are not further values to add. I hope I made myself clear
Possibly, but there might be a better way. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hello. As usual, precise presentation of the problem. You are The Professional.
By the way, I wanted to ask if there are any advantages / disadvantages to using this formula:
[ =SUM(Table[[#Headers];[Values]]:[@Values]) ]
with Your sum and index?
Can't think of any disadvantage. It may even calculate faster ;-) thanks for sharing.
lot of thanks to you .
You are most welcome 😊
Hi , can we get the running total of calculated field, e.g I have a weekly data set of target vs actual, I want to calculate running achievement %(achievement for week 1+ week 2/target for week 1+ week 2 and so on)
In theory, yes. If you get stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Nice tutorial..😀
Thanks 🙂
I tried this as it is exactly what I need but I get the error too few arguments. Not sure why? Using a table and everything just as shown in video.
The error means you have missed one of the components of the formula. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thanks. Suppose that you have 'Manager' and 'Item' column in the table, want to have a running total for Manager&Item, how to reach this?
You can use a PivotTable to do this. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thanks. I know how to use it in PivotTable but if want to use in table, is it possible?@@MyOnlineTrainingHub
my fault! I tried in SUMIFS, it worked in table. Meanwhile, you use 1 in index to refer first column reference. I tried to use Rows to refer last one.Also I found Index is useful than OFFSET since I can use to get eg. last 5 records to make forecast.@@MyOnlineTrainingHub
But what about if I add an entry to row 13, then 14, and so on?
If it's in a Table then you should use the Excel Table Running Total Formula technique.
Super!
Thank you very much!
I would appreciate it if you could help me with the running balance for this data that i have in EXCEL TABLE. I have an opening balance on January 1st of RS. +10,000, received cash on January 5, Rs +20,000, and paid cash on January 8, Rs -4,000. I'm trying to figure out a formula for my data that i have in EXCEL TABLE to show the running balance after each transaction.
Sure, see this tutorial: www.myonlinetraininghub.com/excel-table-running-total-formula
How to limit the running total to the last row in a range . This is to avoid having running total to have same values that goes beyond the range
😁If I had known about this, I could have saved many hours over the years.
Better late than never 😊
thanks, maybe microsoft will learn something from it.
I have this exact problem when adding rows within a finance sheet with money going in or out, however I don't know how to add your alteration.
My formula is:
=IF(C6="VIRGIN",L5+F6,IF(D6="VIRGIN",L5-F6,L5))
The L5+F6 or L5-F6 is I'm guessing where the SUM has to go so I tried
=IF(C6="VIRGIN",SUM($L$5+F6,IF(D6="VIRGIN",SUM($L$5-F6,L5)))) but it returned FALSE.
Can you see where I'm going wrong or doesn't your way apply here? ("F" is money coming in or going out, "L" is the running total, C&D identify the source of the transaction)
Hi John, your closing parenthesis are in the wrong place. It should be:
=IF(C6="VIRGIN",SUM($L$5+F6),IF(D6="VIRGIN",SUM($L$5-F6,L5)))
@@MyOnlineTrainingHubThank you for your reply. Sadly after making the changes I'm still getting the result FALSE. Not to worry. I've been adding rows at the bottom (for many years) and then sorting them by date. I can continue to do that but it would have been nice to have added the rows on the appropriate day. Thanks for trying, it's appreciated.
I've worked out why it isn't working. Its when "VIRGIN" isn't in either C6 or D6. In my original formula it reverts to L5 but not when you make the changes.
I got rid of the FALSE return by changing the formula to
=IF(C6="VIRGIN",SUM($L$5+F6),IF(D6="VIRGIN",SUM($L$5-F6),L5))
However it no longer works correctly, in ROW 33 it takes money from L5 when it should deducting money from the running total in L32. Oh well.
Hi John, Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I need help how to create a CPM in excel
I don't have any CPM examples, sorry.
My solution for Non_tables to get running totals:
=BYROW(E5:E12,LAMBDA(a,SUM(E5:a)))
=MAP(E5:E12,LAMBDA(a,SUM(E5:a)))
IlirU
Hmmm, but that's not going to update dynamically because you have cell ranges hard keyed i.e. E5:E12.
Hi Mynda,
In sheet Non-tables we can use this formula (cell ranges is not hard keyed):
=IF(E5:E20="","",BYROW(E5:E20,LAMBDA(a, SUM(E5:a))))
In sheet Excel Tables we can use this formula:
=IF(Table1[Values]="","",BYROW(Table1[Values],LAMBDA(a, SUM(Table1[@Values]:a))))
when 'a' is a variable.
Same formulas for MAP function, just to change BYROW with MAP.
Best regards, IlirU
Note: Regardless of these formulas I gave above, I think it was an excellent tutorial (as always... your tutorials are excellent). Thank you!
Thanks for clarifying.
We cannot recognize ur formullahs plz zoom
I've been using excle the wrong way for the last 20 years, wow
Better late than never, Chrisy 😊
Ah, I always forget that INDEX can be used for things beyond INDEX&MATCH.
Yes! More INDEX uses here: Excel INDEX Function - 5 Secret Features - do you know them all? th-cam.com/video/pP7AMLJa0Vc/w-d-xo.html
Or if you prefer SCAN:
a : array (2D array, or vector)
[d] : direction argument; 0 or omitted scan by array; >1 by clms;
Thanks for sharing, Sachin!
First view first like
I don't have F keys. :-(
If you’re working On a laptop you usually have to press the fn key to enable them.
Tables are wonky....Sorry. Not a fan. Love the other stuff.
That's a shame, Patrick. For me, Tables are one of Excel's best features.
I would suggest reconsidering the issues that led to your conclusion about tables. I would recommend them strongly for source data, even to the point of regarding any values held outside a table as an error. The gaudy stripes and filter buttons are largely unnecessary but structured references that adjust as the date changes make the workbook more intelligible and more robust. By all means move away from the table for calculation since array formulas conflict with table structure.
@@peterbartholomew7409
hi, hoping I might get a response from this question.
I get paid every week so I'm looking for an app or excel formula that will look at all my income and the dates I get paid, then look at all my bills per month and plot out where to pay them during the month (example.. I have $100, bill is $120, so then it automatically figures out how to spread that out).
I figure a running balance will help with this. I'm guessing I'll need some if, then statements and I'll watch this video.
=SUM($B$4:@VALUES)
That seems to work too.😁
Nice 😉
Thank You!!!
My pleasure 😊