Yes, thanks! I was struggling and the formula builder says I can only use lambda as the function. Otherwise, I get a #NAME error. No I have a reason to use lambda. Thanks!
I liked how you started the presentation with a demo of the actual function, instead of leading us through wondering what's coming. But I LOVED when you started explaining the function with pen and paper. Nice analogue humanist connection. Thank you! 🙏
Wow, thanks. SCAN combined with lambda function works very well in a running decaying series. I was using a too ordinary formula to handle this. Now you made it even more easier for me! For example, one may need to monitor a total budget supporting different items, how it goes down for every expenditure incurred. SCAN with lambda function in it makes it a straight forward approach🙂
The function REDUCE is also very useful. In the last example, using REDUCE instead of SCAN you can obtain directly the cumulative sum of visits for every month.
What I love about scan and reduce is that they are able to make array formulas spill both vertically and horizontally. For example you can transpose the results of filter in the adjacent columns and at the same time make filter calculate vertically. A one cell formula with results in multiple rows and columns
Looking forward to watching your LAMDA video. It's taking me a long time to wrap my head around LAMDA. I am sometimes successful with it, but I don't know why!
Excel is the best Data Analysis tool till date. It is the jack of all but master of none. And that is the USP of this magical tool and also the weakness.
I used the same way for quite a long time. But don't look past the function itself. The idea here is to demonstrate the function, put it in your toolbox, and figure out how to use it later in a novel way. (I haven't yet, but I'll come up with something :-) )
I still don’t understand the point of SCAN so why did he demo it that way and then switch to LAMBDA which made even less sense. I’m sure this function has some niche advantage but I don’t get it so I don’t think it’s gonna apply to my work.
I hadn't heard of choosecol and that spill range syntax before - handy. For a tutorial video I'd suggest naming the a & b parameters as acc & curr to make it more obvious which is which - that's how I name them when writing JS / C# etc
I am glad you found that helpful. Thanks for the suggestion reg. acc, curr. As most of my viewers have no coding experience, I went with a,b to keep it simple.
I think the functionality is pretty new to PowerQuery as well. There was a time in both PQ and DA that to calculate the next sum involved going back to the first record and summing to the current record. It may well be SCAN that came first.
Thanks for all the Tips. Extremely useful Help needed on two topics. How do I use scan function to manage bank accounts? I have debit column, credit column and running Balance. Running balance is prev row balance + current row credit - current row debit. Can scan be used here. One another help is: for my investment tracking How can XIRR be calculated? The columns I have are Stock name, Buy date, sell date, buy value, sell value, current value [Current value is non-zero if sell value is zero, else it is zero]. With this 1. How do I get XIRR for each instrument i.e for each row 2. How do I get current XIRR overall? Thanks, Elango
Great video, what about alternatives to calculatr dynamic moving averages , last 3 davys 7 days monthly quarterly etc ..and best approach from product analytics perfotmance point of view to make business sense of your data
The third example is perfect. Scan obviously saves a lot of typing and makes it easier to read. So definitely a nice thing. The first two examples are a bit... not well explained/sold. E5: =$D5 E6: =$E5+$D6 F5: =$D5 F6: =max($F5, $D6) And then just select E6:F6 and doubleclick the bottom right corner to get it copied to the following lines. If you wanted to make this a single formula for each column then you can use if and isnumber to get the formula on row 5 to handle the fact there are no values on row above, just labels. However changing order of the rows may ruin the fun, there the SCAN would be handy too. The third case can also be solved using a formula with ifs and conditional formatting. But it would look too messy. So I am happy there is a better way! Thanks!
Thank you. I agree we could do with a better / different explanation. That said, SCAN requires thinking about data in ranges and introducing that "new" idea would be a challenge if I had jumped straight to example #3.
I have a worksheet that fundirnos like an inventory. It uses a vba user form to add a new entry for an item checked in or out. Could I use this scan function on a different worksheet to populate an inventory table that updates based on the first worsksheet?
Amazing video as always, but I struggle with a “NAME?” error even though I have followed your instructions, “=SCAN(0;C5:C15;sum)” and yes formula separators are semicolon where I live. Can it be something with the version of Excel?
Hi there... This is normal. The named shortcuts are rolling out to all users of Excel 365. Meanwhile, use this alternative syntax: =SCAN(0; C5:C15; LAMBDA(a;b; SUM(a;b)))
I have a table with hundreds of stock trades. I would like exel to auto generate a new unique ID for each trade as I add it to the table. I would like that ID to stick with the row that contains the trade even when I sort the table by various columns (such as date or name of stock etc.). I can get excel to auto generate a number using concate but that makes the numbers long and awkward. Is there a way to get excel to auto generate a Unique ID for each trade so that I can re-sort by ID and get them back in chronological order?
I saw another video which was about the new checkboxes and she was using NOW with iterative calculation turned on to make a timestamp when the box was checked. If the goal is a chronological sort I might try something like that.
The performance would be better. Plus, as demoed later in the video, many times, you have no idea what the end point of the data is. So you don't know how far to drag or fill the formula down. SCAN helps in such situations.
@chandoo_ hm....but it also applys to SCAN function. If you don't know the size of the range you have to adjust scan function. Why not to use smart tables with the same easy function SUM($A$2:$a3)? The same result and you can always identify which range is used to do the calculation.
What if I want a percent of total docs received by a column of dates. If there is a date it is received but I want to know how many is received by line
@@chandoo_ Still wrong. The total average of the file numbers equals to 4736 but the SCAN function in December gives 4818. I cannot post a photo in the comments.
The average works in a different way than you think. for row 1 - same as the value in cell 1 for row 2 - average of first two valuesa from row 3 onwards - average of (previous average, current value) This is why the results appear wrong. But SCAN is doing what it is supposed to, which is to apply consistent average logic on previously "accumulated" value and the current value. To get running average of the list, you need any of these 3 functions: All of these assume your data is in E3:E12 1) Easy: =AVERAGE($E$3:E3) and drag it down 2) With Scan: =SCAN(,E3:E12,SUM)/SCAN(0,E3:E12,LAMBDA(a,c,a+1)) 3) With Let & MAP: =LET(arr,$E$3:$E$12, arr_count,COUNTA(arr), sq,SEQUENCE(arr_count), MAP(sq, LAMBDA(a,AVERAGE(TAKE(arr,a))))) I am sure there will other shorter and efficient options...
What if you have in collumn B the years showing multiple years (example last year and current year), collumn C month, collumn D showing In the Month Downloads. Collumn D showing year to date downloads. How to calculate collumn D running total within year ? Example from 2023 Jan till 2024 march, it shows the running total in collumn D of Jan-dec 2023…and running total Jan -mar 2024. Next to that is there also a way to apply this as calculated field within pivot table ?
You can use SCAN for such things too. The calculations get a bit complicated. For ex: =LET(years, COL_B, months, COL_C, values, COL_D, nums, SEQUENCE(COUNTA(values)), SCAN(0, nums, LAMBDA(a,b, IF(INDEX(months, b)=1, INDEX(values, b), INDEX(values, b)+a)))) The formula resets accumulator to first month value when month is 1. It also assumes you have only one row per year month combination. If you have daily data, you need to adjust the IF logic.
Chandoo, I have a question, I have a data set comes with so many repeat years , such as from 2010 to 2020 by revenue, then 2010 to 2020 by cost , then item sold … and more , how can I clean this kind of data set ?
You can do this with "let" You createva variable that counts the number of items in the list. Then you use that to create an array containing tjose numbers. Then you create an array where each element is the running total of the previous array. That array is what is displayed in the cell. So itvautomatically fills down.
Can running total be conditional? First row is taken from a table, second row onwards it checks if number in upper cell is negative then taken from corresponding row of table, but if upper cell is positive number, then add upper cell & corresponding row of table. formula is like this in row =B2 and row 3 onwards =IF(F2
@@chandoo_awesome. works smoothly. million thanks, for the solution and for all the knowledge that you share. so next question. my base data table comes from power query where the Column B resides. Is there any way to get same output (like above SCAN function) in power query?
I have the formula down pack typed it in at least 15 times and every time i hit enter this comes up #NAME its just not working and i even did my own spread sheet so i was going from B colume. what am i doing wrong? or is my excel missing something
Thank you so much Sir 🙏🏼, however, I'm stucked on a different problem, how to connect with you, unable to get help from the experts I am familiar with.
Looks like a great function. I can't get it to work though... =scan(0,b2:b20,sum) ... won't work, I just get #NAME?, I tried MAX too. The Lambda alternstive works =scan(0,b2:b20,(lambda(a,b,a+b)) Any ideas why? I am using 365, and it is upto date
No need to worry. I think the named shortcut versions are rolling out to all users of Excel 365 slowly. You might see that option working in the next few months.
All the functional programmers saying “this is new?”. I’m pretty sure this is based on work by Simon Peyton-Jones, one of the main developers of the Haskell compiler (at least before he left MS Research for Epic Games).
This is "new" for millions of Excel users who have never heard about functional programming. It is liberating for those of us who know what FP is and use Excel for analysis.
Hi, by mistake Go to special is pop up in my blank excel sheet and, i am not able to get rid of it. Please help me with this issue. Am not able to work on excel
For those of you familiar with the REDUCE function, you might ask what the point of SCAN is. Well, they actually do exactly the same thing! The difference? SCAN outputs an array of length of 'b', which contains the stepwise result of a REDUCE calculation. You can think of REDUCE as outputting only the last item of the SCAN output. I have not found a good use for SCAN on its own yet. My favourite use case is actually for troubleshooting REDUCE. Since it lets you "step in" to a REDUCE, you can look for mistakes and errors.
If your Excel version doesn't yet have the SCAN feature to directly use the shorthand version of SUM, you will see _xleta.SUM. Replace the formula with the longhand version below and it will work: =SCAN(0, D5:D16, LAMBDA(a,b, a+b))
🚨💡💡 Getting an error?
As I understand, The SUM option is slowly rolling out to non-beta users.
Try this alternative:
=SCAN(0, range, LAMBDA(a,b, a+b))
Thanks it worked
Yes, thanks! I was struggling and the formula builder says I can only use lambda as the function. Otherwise, I get a #NAME error. No I have a reason to use lambda. Thanks!
I am using it but getting notification "You have entered too few arguments for this function" =SCAN(0,C2:C14,LAMBDA(C2,C3,C2+C3))
@@JevVan did you find out how to solve the error? I had the same error with you when I use the formula (without Lambda): SCAN(0,B2:B13,sum).
@@SanjaySinghWT
You forgot the function: SUM
Try this:
=SCAN(0,C2:C14,LAMBDA(C2,C3,SUM(C2+C3)))
I liked how you started the presentation with a demo of the actual function, instead of leading us through wondering what's coming.
But I LOVED when you started explaining the function with pen and paper. Nice analogue humanist connection.
Thank you! 🙏
YES! You said it better than I could have. I love this explanation. "just show me how it works". Thank you, Chandoo.
The way we explain real calculation on paper it's great. 👍
Wow, thanks. SCAN combined with lambda function works very well in a running decaying series. I was using a too ordinary formula to handle this. Now you made it even more easier for me! For example, one may need to monitor a total budget supporting different items, how it goes down for every expenditure incurred. SCAN with lambda function in it makes it a straight forward approach🙂
*Chandoo literally has gold standard Excel skills.*
I wonder if he and Leila are a couple. If not i would be devastated.
bye, bye running total. thank you for the clear explanation about =scan.
Thanks
You are welcome. Thanks for the super 😍
This is what i need to use the MAX of values on rolling basis. A stakeholder uses this as part of a metric/formula they use. Thanks for this.
Thank you for such a simple Formular for running total.
The function REDUCE is also very useful. In the last example, using REDUCE instead of SCAN you can obtain directly the cumulative sum of visits for every month.
Great point. You can also use SUM(CHOOSECOLS(FILTER(...),2)) to get the total directly.
Great content with top-notch explanations. My new favorite Excel expert!
What I love about scan and reduce is that they are able to make array formulas spill both vertically and horizontally.
For example you can transpose the results of filter in the adjacent columns and at the same time make filter calculate vertically. A one cell formula with results in multiple rows and columns
Appreciations for helping us to maximize value from Excel.
HOLY MOLY! I've gotta get up on SCAN.
😍
Looking forward to watching your LAMDA video. It's taking me a long time to wrap my head around LAMDA. I am sometimes successful with it, but I don't know why!
Great job Chandoo. You rock brother.
I tried the LAMBDA with a SUM, and it worked perfectly. Thanks!
Very useful function. Great real scenario example. 👌
Super well explained.
Beautifully explained as ever, Chandoo. Gold Star.❤
Glad you liked it!
Okay, my Excel needed to update I guess, now it works, thanks Chandoo
Excel is the best Data Analysis tool till date. It is the jack of all but master of none. And that is the USP of this magical tool and also the weakness.
Wow, LOVE the detailed the explanation!♥
Very insightful Video. Thank you for your hard work
Thank you, Chandoo. This is fantastic. I will try at work on Monday. :)
Fascinating stuff. I don't see myself ever using it, but that doesn't mean it isn't interesting.
You are loved, Sir.
My formula for a running total in column A is =SUM(A$1:A1) in B1, then copy it down as far as you need. Simple.
Lol, this is between professional vs fancy way of doing
I used the same way for quite a long time. But don't look past the function itself. The idea here is to demonstrate the function, put it in your toolbox, and figure out how to use it later in a novel way. (I haven't yet, but I'll come up with something :-) )
@@nobodynobody1235 And which is which?
I would argue that it is the dynamic array formula that is 'professional'!
@@peterbartholomew7409 And I would argue that if originally this was done in a Table, Excel would automatically fill the formula dynamically
I still don’t understand the point of SCAN so why did he demo it that way and then switch to LAMBDA which made even less sense. I’m sure this function has some niche advantage but I don’t get it so I don’t think it’s gonna apply to my work.
this is great, super i learnt lot sir, many thanks
very clear explanation thank you
Excellent.. really how easeeee is this....
Thanks for sharing 🎉...
This is insightful!
Thanks for sharing
Wow 😮 New learning for tonight ❤
Wonderful!
You can just use the previous summed amount plus the new value. Dead easy and simple to understand for someone not so familiar.
I always used =B1+A2 on cell B2 for running total if A contained numbers, and pull down. Where cell B1 is manually set.
Thank you so much 🙏
Good job Chandoo 👊🏻
thanks, this was really interesting
Thank you chando.
But what about if we use a text for intial value?
baa cheptunnaru Chandoo , Best Wishes !
Good. More power to you from Pakistan.
I hadn't heard of choosecol and that spill range syntax before - handy.
For a tutorial video I'd suggest naming the a & b parameters as acc & curr to make it more obvious which is which - that's how I name them when writing JS / C# etc
I am glad you found that helpful.
Thanks for the suggestion reg. acc, curr. As most of my viewers have no coding experience, I went with a,b to keep it simple.
So it's FOR loop
A bit like list accumulate in power query. Functional programming. Thanks for pointing it out.
I think the functionality is pretty new to PowerQuery as well. There was a time in both PQ and DA that to calculate the next sum involved going back to the first record and summing to the current record. It may well be SCAN that came first.
Hi Chandoo,
Could you please do the video on how to create a fiscal calendar table in excel as we do in power bi using dax. Please, Iam in a need
Thanks for all the Tips. Extremely useful
Help needed on two topics. How do I use scan function to manage bank accounts? I have debit column, credit column and running Balance. Running balance is prev row balance + current row credit - current row debit. Can scan be used here.
One another help is: for my investment tracking How can XIRR be calculated? The columns I have are
Stock name, Buy date, sell date, buy value, sell value, current value [Current value is non-zero if sell value is zero, else it is zero]. With this
1. How do I get XIRR for each instrument i.e for each row
2. How do I get current XIRR overall?
Thanks,
Elango
Excellent
Great video, what about alternatives to calculatr dynamic moving averages , last 3 davys 7 days monthly quarterly etc ..and best approach from product analytics perfotmance point of view to make business sense of your data
Excellent Sir
Awesome….thank you very much
Good stuff!
Awesome. Thanks for sharing
very informative sir, thank you for sharing
SCAN with MAX just gave me a gazillion ideas.
How nice it would be if Microsoft provide us with a list of what is new, at the end of an update.
If it is there and I have missed it, please say.
The third example is perfect. Scan obviously saves a lot of typing and makes it easier to read. So definitely a nice thing.
The first two examples are a bit... not well explained/sold.
E5: =$D5
E6: =$E5+$D6
F5: =$D5
F6: =max($F5, $D6)
And then just select E6:F6 and doubleclick the bottom right corner to get it copied to the following lines.
If you wanted to make this a single formula for each column then you can use if and isnumber to get the formula on row 5 to handle the fact there are no values on row above, just labels. However changing order of the rows may ruin the fun, there the SCAN would be handy too.
The third case can also be solved using a formula with ifs and conditional formatting. But it would look too messy. So I am happy there is a better way! Thanks!
Thank you. I agree we could do with a better / different explanation. That said, SCAN requires thinking about data in ranges and introducing that "new" idea would be a challenge if I had jumped straight to example #3.
I have a worksheet that fundirnos like an inventory. It uses a vba user form to add a new entry for an item checked in or out. Could I use this scan function on a different worksheet to populate an inventory table that updates based on the first worsksheet?
thanks
plz do video in different between lambda helper functions like scan . map reduce
Nice thank you
sir thanks for explning and if we want to know which day has the max value acrding mnth wise, then can we use day function
Great unknown formula...from which version is available?
Its key clickin' good!!!
Amazing video as always, but I struggle with a “NAME?” error even though I have followed your instructions, “=SCAN(0;C5:C15;sum)” and yes formula separators are semicolon where I live.
Can it be something with the version of Excel?
Hi there... This is normal. The named shortcuts are rolling out to all users of Excel 365. Meanwhile, use this alternative syntax:
=SCAN(0; C5:C15; LAMBDA(a;b; SUM(a;b)))
Since one year I'm using this formula in Google sheets
Also the byrow, bycol,reduce, map and scan too
The appeared late 2021 in Excel insiders beta.
Thank you Chandoo!!so useful
I have a table with hundreds of stock trades. I would like exel to auto generate a new unique ID for each trade as I add it to the table. I would like that ID to stick with the row that contains the trade even when I sort the table by various columns (such as date or name of stock etc.). I can get excel to auto generate a number using concate but that makes the numbers long and awkward. Is there a way to get excel to auto generate a Unique ID for each trade so that I can re-sort by ID and get them back in chronological order?
I saw another video which was about the new checkboxes and she was using NOW with iterative calculation turned on to make a timestamp when the box was checked. If the goal is a chronological sort I might try something like that.
What is the advantage over using regular dynamic range using sum or max functions ?example - Sum($A$1:$A1) ?
I was wondering the same. I think it's something related to memory or performance and scan seems to be better.
The performance would be better. Plus, as demoed later in the video, many times, you have no idea what the end point of the data is. So you don't know how far to drag or fill the formula down. SCAN helps in such situations.
@@chandoo_ thank you 😊
@chandoo_ hm....but it also applys to SCAN function. If you don't know the size of the range you have to adjust scan function. Why not to use smart tables with the same easy function SUM($A$2:$a3)? The same result and you can always identify which range is used to do the calculation.
Please watch the full video to understand what I mean by not knowing the size. You can't have tables for calculated outputs or formula results.
What if I want a percent of total docs received by a column of dates. If there is a date it is received but I want to know how many is received by line
Thanks for the nice SCAN function. But when i tried to replace SUM with AVERAGE the results were wrong. What would be the reason?
Can you check again? And if it is still wrong, tell me more details.
@@chandoo_ Still wrong. The total average of the file numbers equals to 4736 but the SCAN function in December gives 4818. I cannot post a photo in the comments.
Zero initial value has no effect in SUM, although in average counts one more number.
The average works in a different way than you think.
for row 1 - same as the value in cell 1
for row 2 - average of first two valuesa
from row 3 onwards - average of (previous average, current value)
This is why the results appear wrong. But SCAN is doing what it is supposed to, which is to apply consistent average logic on previously "accumulated" value and the current value.
To get running average of the list, you need any of these 3 functions:
All of these assume your data is in E3:E12
1) Easy: =AVERAGE($E$3:E3) and drag it down
2) With Scan: =SCAN(,E3:E12,SUM)/SCAN(0,E3:E12,LAMBDA(a,c,a+1))
3) With Let & MAP:
=LET(arr,$E$3:$E$12,
arr_count,COUNTA(arr),
sq,SEQUENCE(arr_count),
MAP(sq,
LAMBDA(a,AVERAGE(TAKE(arr,a)))))
I am sure there will other shorter and efficient options...
I like it, never used
Amazing 👍👍👍👍
Amazing Chandoo!
What if you have in collumn B the years showing multiple years (example last year and current year), collumn C month, collumn D showing In the Month Downloads. Collumn D showing year to date downloads. How to calculate collumn D running total within year ? Example from 2023 Jan till 2024 march, it shows the running total in collumn D of Jan-dec 2023…and running total Jan -mar 2024. Next to that is there also a way to apply this as calculated field within pivot table ?
You can use SCAN for such things too. The calculations get a bit complicated.
For ex:
=LET(years, COL_B, months, COL_C, values, COL_D, nums, SEQUENCE(COUNTA(values)),
SCAN(0, nums, LAMBDA(a,b, IF(INDEX(months, b)=1, INDEX(values, b), INDEX(values, b)+a))))
The formula resets accumulator to first month value when month is 1. It also assumes you have only one row per year month combination. If you have daily data, you need to adjust the IF logic.
Chandoo, I have a question, I have a data set comes with so many repeat years , such as from 2010 to 2020 by revenue, then 2010 to 2020 by cost , then item sold … and more , how can I clean this kind of data set ?
Use Power Query
@@chandoo_ I did , but the value come out with different meaning ….
You can do this with "let"
You createva variable that counts the number of items in the list. Then you use that to create an array containing tjose numbers. Then you create an array where each element is the running total of the previous array. That array is what is displayed in the cell. So itvautomatically fills down.
Sure.. have you tried it? Do share your formulas.
Can running total be conditional? First row is taken from a table, second row onwards it checks if number in upper cell is negative then taken from corresponding row of table, but if upper cell is positive number, then add upper cell & corresponding row of table. formula is like this in row =B2 and row 3 onwards =IF(F2
You can do it. I am not sure if I got your logic right, but try this:
=SCAN(0, B2:B100, LAMBDA(a, b, IF(a
@@chandoo_awesome. works smoothly. million thanks, for the solution and for all the knowledge that you share. so next question. my base data table comes from power query where the Column B resides. Is there any way to get same output (like above SCAN function) in power query?
Super function🕵♂🕵♂🕵♂
I get #VALUE so much and Excel is horrible with troubleshooting lol, I keep changing the number format and always get #VALUE every time.
that's beautiful. yet we still can't do for loops in excel.
Do you provide Excel and power bi course
Yes, check this please - chandoo.org/wp/power-bi-course/
I have Office 2021 but there is no SCAN function in my excel. What to do ?
Thank you, @Chandoo, very amazing funtion! and Awesome too!
Superb
I have the formula down pack typed it in at least 15 times and every time i hit enter this comes up #NAME its just not working and i even did my own spread sheet so i was going from B colume. what am i doing wrong? or is my excel missing something
I cannot get the answer as per the example. Is there anything to get the answer?
Genius
Hi is the scan function available in Excel 2016, I dont see it there
Nope. You need Excel 365 or web versions for this.
Thank you so much Sir 🙏🏼, however, I'm stucked on a different problem, how to connect with you, unable to get help from the experts I am familiar with.
Looks like a great function.
I can't get it to work though...
=scan(0,b2:b20,sum) ... won't work, I just get #NAME?, I tried MAX too.
The Lambda alternstive works =scan(0,b2:b20,(lambda(a,b,a+b))
Any ideas why?
I am using 365, and it is upto date
No need to worry. I think the named shortcut versions are rolling out to all users of Excel 365 slowly. You might see that option working in the next few months.
@chandoo_ that's great, thank you! Again great video👏
I also have EXCEL 365, and it works with the alternative
Try this
Running Totals
SCAN(0,DataTable[Column1],LAMBDA(a,b,a+b))
Max
SCAN(0,DataTable[Column1],LAMBDA(a,b,MAX(a,b)))
Running Totals
*SCAN(0,DataTable[Column1],LAMBDA(a,b,a+b))
Max
*SCAN(0,DataTable[Column1],LAMBDA(a,b,MAX(a,b)))
All the functional programmers saying “this is new?”. I’m pretty sure this is based on work by Simon Peyton-Jones, one of the main developers of the Haskell compiler (at least before he left MS Research for Epic Games).
This is "new" for millions of Excel users who have never heard about functional programming. It is liberating for those of us who know what FP is and use Excel for analysis.
Need help, error function is retuning #NAME? error
=SCAN(0,B2:B6,Max)
=SCAN(0,B2:B6,Sum)
Simple explanation chandoo Anna thank u. But im getting "Invalid Name Error" while trying this in 365, any suggestions?
As I understad, The SUM option is slowly rolling out to non-beta users.
Try this alternative:
=SCAN(0, range, LAMBDA(a,b, a+b))
@@chandoo_ thanks anna lambda works. Yup i think its because of SUM function
my excel version has no scan fuction, please send link to download scan add on
I am getting this error when I try it, #NAME? what could be the reason and how can I fix it?
please send link to down scan add on excel function
Hi, it does not function like shown in your video, according to Microsoft I had to use the lambda function!
Hi, by mistake Go to special is pop up in my blank excel sheet and, i am not able to get rid of it.
Please help me with this issue.
Am not able to work on excel
Seems like a trivial function that is easily performed by adding.
For those of you familiar with the REDUCE function, you might ask what the point of SCAN is. Well, they actually do exactly the same thing! The difference? SCAN outputs an array of length of 'b', which contains the stepwise result of a REDUCE calculation. You can think of REDUCE as outputting only the last item of the SCAN output.
I have not found a good use for SCAN on its own yet. My favourite use case is actually for troubleshooting REDUCE. Since it lets you "step in" to a REDUCE, you can look for mistakes and errors.
Great points. I found SCAN useful in some very particular situations (like running total).
I love REDUCE too.
Your file has this formula.. =SCAN(0,D5:D16,_xleta.SUM). What is xleta.SUM?
If your Excel version doesn't yet have the SCAN feature to directly use the shorthand version of SUM, you will see _xleta.SUM. Replace the formula with the longhand version below and it will work:
=SCAN(0, D5:D16, LAMBDA(a,b, a+b))