Excel - Best Way For Running Totals - Episode 2590
HTML-код
- Опубликовано: 7 июл 2024
- Microsoft Excel Tutorial: Best Methods for Creating Running Totals in Excel | Comparison of 3 Formulas | MrExcel.
Microsoft Excel how-to video about Running Totals in Excel.
In one of my Bing Shorts, I showed two different ways to do Running Totals in Excel.
And then, an interesting comment from Carlo in Italy with a running total formula that I've never seen before.
To download the data from today, go here: www.mrexcel.com/youtube/Tnkyq...
I had to bring that formula to Excel to visualize how it was working. It was cool that it used a colon next to INDEX. But is there a simpler way?
In the video, I then compare four different Running Total Formulas. Which are easiest to enter? Which are easiest to explain? Which run the fastest? Thanks to Zack Barresse for teaching me how =SUM(Number,Text) ignore Text without giving an error. Thanks to Charles Williams for the Formula Speed whitepaper and for the Fast Excel tool to measure the speed of the formulas.
You will also catch a Nancy Faust preview of the Spinning Wheel song to entertain you while we wait for the slow versions to calculate.
Welcome to episode 2590 of MrExcel's RUclips channel! In this video, we will be discussing the best way to create running totals in Excel. This topic was inspired by a comment from Carlo in Italy on one of my Bing shorts videos. Carlo shared a unique running total formula that caught my attention and led me to explore different methods for creating running totals in Excel.
In this video, we will be comparing three different methods for creating running totals: the "Bill Kindergarten" method, the "MrExcel" method, and Carlo's method. Each method has its own advantages and disadvantages, and we will be discussing the criteria for judging the best method. These criteria include having the same formula all the way down, being easy to explain and teach, and impressing coworkers.
The "Bill Kindergarten" method, which I used for 12 years while working in accounting, involves using two different formulas to calculate the running total. The "MrExcel" method, which I currently use, involves using the SUM function to add the cell above and the cell to the left. Carlo's method, on the other hand, uses the INDEX function to point to a specific cell and then uses the SUM function to add the cell above and the cell to the left.
After testing each method using Charles Williams' Fast Excel tool, we found that the "Bill Kindergarten" method was the fastest, followed by the "MrExcel" method and then Carlo's method. However, the "MrExcel" method received the highest score due to its simplicity and ease of explanation. Carlo's method, while impressive, may be more difficult to teach and understand for those who are not familiar with the INDEX function.
In the end, the best method for creating running totals may depend on personal preference and the specific needs of the user. I would love to hear from you in the comments below about your preferred method for creating running totals and how you teach it to others. And if you enjoyed this video, don't forget to like, subscribe, and ring the bell to be notified of future videos. Thank you for watching and see you next time on MrExcel's RUclips channel!
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-mrexcel-...
Table of Contents
(0:00) Running Totals in Excel
(0:14) Bing Shorts explanation
(0:31) Formula from Carlo
(1:08) 8 Functions change when colon-adjacent
(1:40) Why not shorten INDEX?
(2:06) =Up plus =Left with two formulas
(2:45) Zack Barresse and SUM(Up,Left)
(3:15) MrExcel way with expanding range
(4:15) Judging criteria for best running total
(4:45) Charles Williams fastest Running Total
(5:07) Simple formula: less than a second
(5:24) Expanding range: 26.3 seconds
(5:44) Carlo 36.4 seconds
(5:58) Why the simple formula is faster
(6:39) =SUM(Left,Up) is winner
(7:18) Wrap-up
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Bill Kindergarten method
Bing shorts
Cell references in Excel formulas
Expanding range in Excel
Fast Excel by Charles Williams
Formula speed in Excel
INDEX function in Excel
MrExcel method
Running total formula
Running totals in Excel
Teaching coworkers Excel techniques
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...
I use that SUM function with ignoring text. I love that unsophisticated one lol
Dear Bill,
With the new dynamic SCAN function it is much easier, and the result spills over:
=SCAN(0,D5:D13,LAMBDA(x,y,x+y)) 🤗
yes i love this method.
Congrats, this one takes the cake, IMO. Max. points in my book.
By far the fastest, and easy to explain, too. Doesn't work in Excel Tables, though.
@@GeertDelmulle If the table has a column headed "Value", in the "Total" column, simply enter the formula, and the result will pour into the entire column:
=SUM( Table1[[#Headers],[ Value]]:[@Value] ) 🤗
@Geert Delmulle I use SCAN when I can but my data sets are slmost always converted to Tables. Then I use the Header colon 1st data cell method that I mentioned in my separate earlier comment. (Which I believe is whst you were suggesting in your above comment)
Fun and informative video, Mr Excel!!!!
Super interesting survey!!✌
Remembered, before having SCAN, how I did this, single cell, using MMULT
-for running totals
=LET(a,B2#,r,ROWS(a),s,SEQUENCE(r),MMULT(IF(s>=SEQUENCE(,r),TRANSPOSE(a),0),s^0))
-and even running "subtraction" 😉
=LET(a,B2#,r,ROWS(a),s,SEQUENCE(,r),TRANSPOSE(MMULT(s^0,IF(SEQUENCE(r)>=s,a,0))))
What is interesting with this is that because of its "squareness" mmult concept, excel runs out of resources for vectors larger than 7327 elements.
Believe it or not, Excel largest square sequence that can "print" or calculate is only =SEQUENCE(7327,7327)
Anyhow, SCAN is fast and easy with a single initial acumulator and an array, the challenge is to reset the initial value by row or by column for 2D super large arrays. Have a study on this one, one day will get published at the forum.
Again, Super cool to test speeds of different methods. ✌🙏
😊For running totals, byarray , byrow or bycol (resets initial acumulator value by each row or by each col) found this lambda that I posted at the forum, thread ASCAN, back on Sep 30 2021😊:
ASCAN(ar,[d]) d,omitted by array ; d,-1 by row ; d,1 by col
=LAMBDA(a, [d],
LET(
y, IF(d = 1, TRANSPOSE(a), a),
s, SCAN(0, y, LAMBDA(v, a, v + a)),
x, s - IF(d, INDEX(s, , 1) - INDEX(y, , 1)),
IF(d = 1, TRANSPOSE(x), x)
)
)
I use running totals in tables in excel as I typically need table structure with index column to analyze account activity. I use the Sum(header row in column:first value in column) from Jon at Excel Campus. This application works well for adding/deleting items in the table without bastardizing the runttl function. That said, I love the simplicity of what you demonstrated.
Very interesting Bill, thanks. I'll just SCAN the comments for any differing suggestions.
Hi
One more way with one formula adding cell above
In the header cell, type 0 and change the custom formatting to display the header description in all the categories for positive values; negative values; zero values; text values.
Then just add the value to the left to the value above
I love the =SUM(E4,D5) tip! Simplicity is best! My biggest beef with this type of running total, though, is that inserting or deleting rows causes cell reference issues. To overcome that, I use a relative cell reference in Name Manager called CellAbove. Then my running total would be =CellAbove+D5, and my first row would be a hard keyed “opening balance” (when working with accounting data) to avoid the different formula. Now I should be able to just use =SUM(CellAbove,D5). Thanks!!!
This works well in a range, and it works especially well in an Excel Table (where the cells are structured references). E5 =N(OFFSET(E5,-1,))+D5
Amazing. Thanks for the share.
@Bill I do not have the add-in to calculate the speed, what if you wrap Carlo's formula in a LET statement
=LET(a,INDEX($D$5:$D$13,1),SOMME(a:D5)), so that it will calculate the INDEX($D$5:$D$13,1) only once ?
Thank you Bill for this interesting Video
Thank you for your video Bill, very interesting.
I like the Scan/Lambda combination although sometimes it does not suit your situation.
For example, when producing the running total for sales for the month and you have 5 days to go the formula repeats the last value for the remaining cells.
I had to resort to this: =SCAN(0,C8#,LAMBDA(a,b,IF(VALUE(OFFSET(b,-6,))
In a Table (vitually all my data sets are in Tables) click on header, then insert a colon, then click on the cell below the header, then wrap the expsnding range in SUM (or SUMIFS, etc). Really fast to set up.
Me too
Thanks Bill. One additional point for consideration. I know you don't like tables but if this is being done inside a table (A1:B10) where A is number and B is running total the formula for column B = SUM (A2,B1) will break (and the user probably won't know it) if the user inserts a row in the the middle of the table (lets say new row = 5) because the formula in the new row will be correct (SUM(A5,B4) but row 6 will not adjust accordingly but instead will be SUM(A6,B4). To combat this I've moved to using the formula =SUM(A2,OFFSET(B2,-1,0)) which will still be accurate if a row is inserted in a table. It loses points on "explainable to someone else" criteria but better than breaking and still much faster than SUM(A$2:A2).
You can also use the Sum(header row in column:first value in column) which I learned from Jon at Excel Campus. I use it frequently in "stuff" that I do in account analyses. I structure the data in PQ and add an index column to always restore the original order.
00:28 - Bill, I have never been annoyed by any of ur content.
Very interesting analysis of which formula works faster. I often use the Mr. Excel way. However, I will consider going back to Bill kindergarten way. It just makes more sense even if my spreadsheets are relatively small by comparison of 100,000 rows.
Thank you Mr. excel! I'm curious about the time that the SCAN solution posted by someone below may takes compared to the other methods
=SCAN(0,rng,LAMBDA(a,b,a+b))
Bill K's way won't work when we're sorting the data. Right?
But can you create a formula that takes over an hour to do 100,000 cells?
Can you do one video showing the splll vs copying the formula😜
Like a few others who have commented, I like SCAN, but suspect it would come second on your scoring system
I am very intrigued by the "Someone inserts a row in the middle" problem. I might need to change the scoring system.
This only refers to 2 cells and is easy to explain. Too slow? E5=N(E4)+D5
I like this one. The N() function will ignore the heading.
I’ve been working on formula yesterday to reset an Index number when the data changes in any of three columns =IF(AND(Q36=Q35,O36=O35,M36=M35),R35+1,1), I'm trying to get it to spill but it's getting the better of me.
I agree this will not spill. Could you enter it in R36 and copy down to the bottom of your data?
That is working in my test here.
Shout out to the MrExcel Forum Some one came up with this Formula =SCAN(0,SEQUENCE(ROWS(I36#)),LAMBDA(a,b,IF(b=1,1,IF(AND(INDEX(I36#,b,6)=INDEX(I36#,b-1,6),INDEX(I36#,b,4)=INDEX(I36#,b-1,4),INDEX(I36#,b,2)=INDEX(I36#,b-1,2)),a+1,1)))) my head hurts just looking at it.
@@lcorcoran56 Mine too.
Running totals in Pivot Table.
The data exist in its own Table, not range. A pivot table or other reporting methods report on that data, not in that data!