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)) 🤗
@thepoolstatguy
Жыл бұрын
yes i love this method.
@GeertDelmulle
Жыл бұрын
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.
@JoseAntonioMorato
Жыл бұрын
@@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] ) 🤗
@richardhay645
Жыл бұрын
@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)
@excelisfun
Жыл бұрын
I use that SUM function with ignoring text. I love that unsophisticated one lol
@Excelambda
Жыл бұрын
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. ✌🙏
@Excelambda
Жыл бұрын
😊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) ) )
@notesfromleisa-land
5 ай бұрын
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.
@kleinboertjie
Жыл бұрын
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
@ricos1497
Жыл бұрын
Very interesting Bill, thanks. I'll just SCAN the comments for any differing suggestions.
@drsteele4749
Жыл бұрын
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
@davidabuang
Жыл бұрын
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!!!
@excelisfun
Жыл бұрын
Fun and informative video, Mr Excel!!!!
@SyedMuzammilMahasanShahi
Жыл бұрын
Amazing. Thanks for the share.
@richardhay645
Жыл бұрын
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.
@henryg5735
Жыл бұрын
Me too
@KO1967
Жыл бұрын
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).
@notesfromleisa-land
5 ай бұрын
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.
@realBkay
Жыл бұрын
00:28 - Bill, I have never been annoyed by any of ur content.
@nadermounir8228
Жыл бұрын
Thank you Bill for this interesting Video
@paulsingleton6071
Жыл бұрын
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,))
@c.e.bingham2079
Жыл бұрын
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.
@einoconsult5563
Жыл бұрын
@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 ?
@BhavyaGupta
Жыл бұрын
=SCAN(0,rng,LAMBDA(a,b,a+b))
@kurtneubek3200
Жыл бұрын
This only refers to 2 cells and is easy to explain. Too slow? E5=N(E4)+D5
@MrXL
Жыл бұрын
I like this one. The N() function will ignore the heading.
@davidjefferson6986
Жыл бұрын
Like a few others who have commented, I like SCAN, but suspect it would come second on your scoring system
@MrXL
Жыл бұрын
I am very intrigued by the "Someone inserts a row in the middle" problem. I might need to change the scoring system.
@RambozoClown
Жыл бұрын
But can you create a formula that takes over an hour to do 100,000 cells?
@carlo_migliari81
Жыл бұрын
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
@thepoolstatguy
Жыл бұрын
Can you do one video showing the splll vs copying the formula😜
@Ticky66MN
Жыл бұрын
Bill K's way won't work when we're sorting the data. Right?
@brianspiller9075
Жыл бұрын
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!
@lcorcoran56
Жыл бұрын
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.
@MrXL
Жыл бұрын
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.
@lcorcoran56
Жыл бұрын
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.
Пікірлер: 38