How to Calculate YTD (Year to Date) Values in Excel using the SCAN function

If I write Sales in Rows .. Than How I use Lamda Function? PLZ Somebody Help me

your knowledge is from another world! hahaha thank you lei :)

Ho to deal the situation where our arrays are horizontal spreading to multiple monthly ans yearly columns. Are we able to achieve the yearly sums through dynamic formulas. I find difficulties using dynamic arrays when we have months and years data.

Excellent. Thank you Leila !!

@Leila Gharani, I'm having trouble finding more complex uses of SCAN. Have you covered SCAN further? Or know of where I can find more complex examples?

Great video, well explained. Unfortunately, I cannot get it to work in a Table. You use it adjacent to the Table, but I don't see it being used like that in practice.

Wonderful tutorial 👍🌹❤

Good one❤

I needed a way to reference another cell not via a fixed reference but a way of calculating whichever other cell I needed. Google did not find the OFFSET function for me, and it's exactly what i was looking for, so thank you!

Pardon my ignorance. I could have done this using a simple excel formula which is much easier to understand than use Scan, offset, Lambda etc. What's the usecase for SCAN function?

Quite interesting

this is awesome and very interesting. I have only one query can this scan function worked horizontal also?

Just come across this scan functio when I press =S in a cell. Nice video.

"Aren't really for everyone" - so honest.

I am so sad I only found you lately ; you are my excel superhero 🎉

Excellent explanation of SCAN. I am an old-school LISP programmer. I am so glad that finally functional programming has arrived in Excel. Thus I prefer single-cell formulas. Thanks to your video I can add SCAN to my kit of powerful tools.

copy paste kiya app dusara channels se

What’s the point of the 0*a? It’ll always be 0

Does this work across i.e. can I get it to spill across?

Honestly, I am still not seeing the value of the LAMBDA function or its helpers. It feels like I could have done anything that these functions do in a more intuitive matter. For example, in this case I would have just written the array formula =SUM((--YEAR($A$2:A2)=YEAR(A2))*$B$2:B2) and dragged it down. It seems more intuitive to read later to remember what I was trying to do, and the same thing would go to someone else - I'd have to run every LAMBDA that someone else did myself to understand what the user did (and that it's free of bugs/limitations).

I guess if you have a big workbook that many people use for repetitive tasks, you could develop the formulas for later use, but then people have to remember the function name, and it'd only work on that workbook. The fact it's also auto-extending the formula to the length of data is handy, but having a table in Excel would do the same thing...

Nice function

Thanks Leila for your great explaination, it is will be an alternative for fixed asset depreciation calculation. right?

You did not say if the excel file you are working on will attach itself to the email prior to send.?

U always have something that i am seeking for.

Not feeling super comfy with LAMBDA yet. Although not perfect I managed to find a solution to replace the OFFSET function.
New updated formula would look like this: =SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(INDEX(Sales[Month],ROW(b)-1))<>1,a+b,b)))

Please let me know if you found a better solution! 😉

You forgot to put the automatic subtitles. Could you please so kind put them?

UDF dead! Long Life to Lambda!

hi could you tell me how to scan ducoment in execl and add link of ducoment to cell
I wnat to when I push scan command bottom start scanner to work and scan my document
thanks

Tried, working , great solution.

Thanks for this clear explanation but a little complex. maybe easier by VBA.

excellent

YOU ARE HEAVEN SENT!!! I wish you have an Excel book for ordinary Joes like me.

For Finally Scan function is used for Cumulative calculation, I love this formula, Thanks for your videos
😊

Hi Leila, I would like to know that how to send emails from "get a row from excel" in power automate but from multiple rows.. pls guide.. thanks in advance

Very good

this is more confusing than a number theory seminar at harvard

Since it's getting close to the end of the year how about some tips on how to layout yearly reports

Monthly data is horizontal, NOT vertical.

Excellent video. Looks like DOSUBS function of my hp48G!

Why is my excel not recognising the scan function? It doesn't come up.

wonderful helpful function and best quality course . Thank you lovely and talented Leila

This was extremely helpful. Thanks to scan plus lambda I was able to calculate values adding up per month and finally finding the month with the highest value without pivot and fully dynamic.

Is there any idea of timeframe for this to reach actual Enterprise subscribers? I mentioned this in a response below, that I'm almost to the point of downvoting these types of videos. I love tips; however, tips for functions that aren't even available make no sense. I might as well bookmark this and watch it again in a years time when Microsoft makes it available. Please do not take this as disrespect. I've watched many of your videos that offered great insights to functions and features I can actually use. This does nothing to assist me with my day to day.

I am not sure it is simplier but without lambda fonction and (unfortunately?) without Table you can use: SUM(B$2:B2*--(YEAR(A$2:A2)=YEAR(A2)))
with A the column of dates and B the values of sales (or whatever). Without Table you have to drag it till your last line or the entire column ...

what lambda formula if i have 1 other parameter such as name product? so i want to calculate every sales of product year to date?

do you really need the a*0 in the OFFSET? why not just give the accumulator b when the year rolls over?

Never knew about this SCAN function. Maybe in the near future I can use this. I need to create a list of possible functions with a link to her vids. Thank you!

I always use cumulative function all the time. I just want to say this is absolutely ridiculous. there is a very simple way to do cumulative. i always do it like this formula Column B first formula first cell =A1, then in cell B2 you would do =B1+A2 and drag this formula down till where ever you need. Means what kind of ridiculousness is this video!!!!

All this effort for +\X , around since the 1960s?