Thankyou Sumit. This is very useful for us. Like to track 7 consecutive days of working or consecutive repeat defaulters. Earlier we had to use some complicated nested countif formulas.
Yes, if you're using the SUM function. Else, can also be used as shown in example 3, where a counter is used that increments by 1 whenever a cell matches the given criteria.
Great video!! For the consecuitive values example, I believe you could also concatenate all of the statuses into a single text and then use the find (or is it search?) function to see if "absentabsentabsent" appears in the string?
hello, nice video to understand SCAN. Though I am not very convinced with the last example, I find it more hard to understand (for others) than a simple textjoin and trying to find "AbsentAbsentAbsent" in the text. Plust that one is working in earlier excel versions too, not only 365.
Excellent video. I have a question. I think it is not possible to implement a conditional format that contains a LAMBDA function. How can we make a conditional formatting so that the cells where the Max is greater than 2 are highlighted in red? Thanks, Sumit.
Dear Sumit In the "Attendance" spreadsheet I created a formula that spills: =VSTACK("Status",BYROW(B2:G6,LAMBDA(x,IF(MAX(SCAN(0,x, LAMBDA(counter,curr,IF(curr="Absent",counter+1,0))))>2,"Investigate","All Good")))) Hope you like it. 🤗
Dear Sumit, I created a unique formula for the "Running Total from Formula" spreadsheet: =LET(_salesdata,FILTER(Table3,Table3[Product]=G1), _totalsales,SCAN(0,CHOOSECOLS(_salesdata,5),SUM), _header,HSTACK(Table3[#Headers],"Running Total"), _result,VSTACK(_header,HSTACK(_salesdata,_totalsales)), _result) Hope you like it. 🤗
This formula will make it spill =BYROW(B2:G6,LAMBDA(a,IF(MAX(SCAN(0,a,LAMBDA(counter,curr,IF(curr="Absent",counter+1,0))))>2,"Investigate","All Good")))
One more useful and interesting function, you explained it very nicely... ❤❤❤❤
Very helpful. The only way I've done this before is to use a recursive lambda function and offset, but scan makes it much easier
Thankyou Sumit. This is very useful for us. Like to track 7 consecutive days of working or consecutive repeat defaulters. Earlier we had to use some complicated nested countif formulas.
you explained it very nicely.
Thank you 😊
Good explanation.
Basically the formula says, from 0, scan the given range, while scanning SUM the range.
Yes, if you're using the SUM function. Else, can also be used as shown in example 3, where a counter is used that increments by 1 whenever a cell matches the given criteria.
Great video!!
For the consecuitive values example, I believe you could also concatenate all of the statuses into a single text and then use the find (or is it search?) function to see if "absentabsentabsent" appears in the string?
Very interesting video thank you Summit
Sir, thank you for explaining the formula in detail. Need to learn LAMBDA first, then practice SCAN to get the results shown in the video.
You can learn about LAMBDA from here - ua-cam.com/video/9G4IIYHySoc/v-deo.html
graet video!❤
hello, nice video to understand SCAN. Though I am not very convinced with the last example, I find it more hard to understand (for others) than a simple textjoin and trying to find "AbsentAbsentAbsent" in the text. Plust that one is working in earlier excel versions too, not only 365.
Excellent video. I have a question. I think it is not possible to implement a conditional format that contains a LAMBDA function. How can we make a conditional formatting so that the cells where the Max is greater than 2 are highlighted in red? Thanks, Sumit.
Great video! Next time can you explain REDUCE function?
Yeah, have it on my list for make videos for all the LAMBDA helper functions, including REDUCE
Dear Sumit
In the "Attendance" spreadsheet I created a formula that spills:
=VSTACK("Status",BYROW(B2:G6,LAMBDA(x,IF(MAX(SCAN(0,x,
LAMBDA(counter,curr,IF(curr="Absent",counter+1,0))))>2,"Investigate","All Good"))))
Hope you like it. 🤗
using max on a scan was interesting
Can you please create video, alternative way for Solver add in
Scan formula explained with relavent examples
❤ fantastic ❤
Ah, now it makes sense. Wasn’t sure what it could do.
Mind-blowing
Dear Sumit,
I created a unique formula for the "Running Total from Formula" spreadsheet:
=LET(_salesdata,FILTER(Table3,Table3[Product]=G1),
_totalsales,SCAN(0,CHOOSECOLS(_salesdata,5),SUM),
_header,HSTACK(Table3[#Headers],"Running Total"),
_result,VSTACK(_header,HSTACK(_salesdata,_totalsales)),
_result)
Hope you like it. 🤗
The 3 th example didn’t spill, I think byrow function is more efficient than scan
This formula will make it spill =BYROW(B2:G6,LAMBDA(a,IF(MAX(SCAN(0,a,LAMBDA(counter,curr,IF(curr="Absent",counter+1,0))))>2,"Investigate","All Good")))
Wow 😮
Awesome
👍👍
1st Comment ❤