To create a generic iterative formula?

Just trying to understand the concepts, which it seems I now need to do.

Anthony

Keep in mind though that LET() and LAMBDA() only exist in the latest and greatest Excel versions, which currently means Office 365 subscription based Excel.  For example I have an up to date version of Excel 2019 standalone for the desktop, and those functions are not available there (I'm not on the Office 365 subscription model at home).  So if you are just doing stuff for your usage, or you know for sure anybody you shared a sheet with would be an Office 365 client, then you're okay.  But if you build something that someone like me tries to leverage, it won't work...

---

»bp

Thanks Bill, well noted. I am learning so as to be ahead of the curve when it eventually rolls out, hopefully more developed. It is very apparent that Google as yet has not much idea about Lambdas. Anthony 

Well a couple of informative pages you could check out...

• The New Excel LET Function: How to Use It & Why You'll Love it
• LAMBDA: The ultimate Excel worksheet function - Microsoft Research

---

»bp

I've seen the second and the first is now too basic for my needs.
I've been doing this practically full time for several weeks now.
I find there's not much out there at all and what there is, is basic.

Anthony

so my best source of learning is in here in EE, which is a joy.

Gary I have copied your message to my notes for further study, but methinks it is way above my head these days. Looks like interesting exploration though. Thanks again Anthony

In programming, an iterative construct is usually a loop, which usually iterates a set number of times using something like
For Count = 1 To 100 By 1
or iterates until some condition is met, like
While Remainder > .01

Understood. But.

It has taken me ages and ages grappling with formulas that iterate through vast swathes of Excel text.
And still I struggled.
That is until a few hours ago when MAY BE the penny dropped, so now I have two pennies.
I need to "see stuff". I think that's why I struggle so much with the programmers' world of working on what you can't see except with your "mind's eye". Abstract thought.
I'm not sure if it is a function (pun intended) of age as some things become harder, like remembering things, or that I have never taken training in it at an earlier age so its' a bit tough now to learn new tricks.

I have quoted your above because what hit me has been staring me in the face since day one (a few weeks ago now), that where we have a Structured Table and a [Field] (i.e. a Table#Header), then using that fieldname in a formula with an @ refers to "this row".
Now intellectually I know that no @ means "this column". BUT. And here it is, the thing. USing the fieldname in a formula without the @ means

"go do this on every row in this column".

So that is a loop, but it isn't a loop. It's not going round and round, it's repeating over and over like the same command listed in a list.
Granted the C# behind the scenes might be looping, but that's not visible.

It's the same command repeated as many times as there are rows, which is the archetypal reason for creating a loop.

So I was looking for some sort of loop in my formulas and with the exception of one actual loop (in a LET)  written by Sir Zorvek, I have never seen one. Hence flummoxed.

Now maybe I hope I have twigged and can read these long and complicated formulas with a new view of the world.

Anthony

I remember reading somewhere about a model of how we learn and remember. The model proposed that when we learn we hang our new knowledge on the branches of our internal tree of knowledge. The concept is that learning is easier when you have a foundation of similar knowledge to which you can relate the new concept, and remembering is easier the more branches you have available on which to hang the new knowledge.

One of the most powerful examples, I think, is that most professions have their own language/jargon. The reason for this is to be able to talk more easily and precisely about the concepts pertinent to that profession. When I say accrual or depreciation or working capital, I am sure that you know immediately what I am talking about in Accountant lingo, but imagine having to try to explain to someone about why they are still being dinged on their profit statement for a purchase they made last year, without using the word accrual.

It sounds to me that you are trying to learn about what most people find to be among the most advanced programming concepts on an abstract level without having enough practical experience. I would suggest that when people learn about programming they typically go through these stages:

1. Copy a small example of code, make it work, understand it.
2. Expand the sample code to handle a similar example.
3. Learn a few new programming concepts.
4. Solve a simple problem using the newly learned concepts.
5. Over a period of a few months, repeat steps 3 and 4.
6. Start solving more complex problems. Along the way read about how others have solved similar problems and incorporate their ideas and code into your solution.
7. Learn a second programming language.
8. Learn a third programming language.
9. Learn about language concepts at an abstract level, often to be able to talk about a programming language or to compare one language to another.

I think that you have jumped into reading about the new commands introduced (apparently still in Beta) into Excel, and want to learn about them in the abstract without first having suffered through the problems that these new commands are trying to solve. Not only that, but you have started by wanting to learn about the most advanced potential use of the command (recursion), which I would contend will almost never be used. I have been helping users with their workbooks for over 45 years, and I would say that a few SUM calculations and simple charts is the most complexity in 95% of the workbooks. Fewer than 1% of the workbooks that I see use named ranges, pivot tables, or VBA. From what I have seen, most of these more advanced concepts enter into workbooks created by programmers in a central IT department which users use without having to understand how they work.

I don't want to extinguish your curiosity, but I suggest that you learn about the concepts in this thread faster if you start by describing the problem you hope to solve using iteration or recursion. Create a sample workbook and post it with a description of your problem. Hopefully there will be an Expert on E-E who has seen this type of problem before and can set you on the right track with a direction to go and an example of how to solve it. You then solve your problem, with more help if you need it. In the process of solving your problem with an actual example you will learn about the concepts needed to solve the problem. After you have solved a few more actual problems, perhaps with some more help, over time you will start to recognise the patterns in how to solve these kinds of problems and will come up with your own directions to take to find solutions. Then, if you are still inclined, when you read articles about the concepts discussed in this thread you will have some branches on your tree on which to hang the concepts in those articles and they will make a lot more sense.

I am also struggling with the new "@" in Excel, but I have a feeling that your new penny might have a hole in it :) The example you chose probably only works when you are referring to a cell or column via a label at the top of the table. You need to slightly alter your language if you want to describe how @ works for a row in a table. Also, @ applies to ranges as well as tables. However, here is an example of how @ was easier to understand by describing its use in one example. I, then, pointed out that you probably have to learn about a few more uses of @ before you can fully grasp it.

I should explain that I am learning by doing, with masterful help from the denizens of EE.
I am the "user", I don't really help others in real life, though always enjoy assisting when asked. Most of my help to others is on here when I am in "answer" mode. My various articles need updatng, but some are still apt.

I distinguish between abstract and not in so far as in Excel to date of a few weeks ago, every calculation I could see in a cell. One result per cell. That was the old way.
Now we have the new way, many results in many cells and many calculations out of sight.
Hence my having to adjust to abstract thought.

If you would like to see a file I have reverse engineered to compare DAs with Pivot Tables just say the word. It is full of Lambdas and Lets so you will need those, as well as dynamic arrays where each one replaces hundreds of volatile formulae.

Anthony

The example you chose probably only works when you are referring to a cell or column via a label at the top of the table.

Yes a Structured Table, using Structured References.

I can't think of a better way to learn about Excel concepts than to actually apply it in a workbook. Although I am an Excel Expert, I learn new things almost every time I answer someone's question by applying another Expert's idea (with credit) to a sample workbook. Your question is the first time I heard about LET, LAMBDA, and that these two new functions are only available in Excel 365.

Unfortunately, I have learned the hard way that many users are reluctant to pay $100/year to update their Excel, and so won't be able to use these two new functions. For this same reason some aren't able to use Tables. Also, I have found that some Excel features don't work on the Apple version of Excel. For this reason I tend to stay away from the latest and greatest, since I still have clients and business partners using Macs and Excel circa 2014.

yes, and I am on Mac. There are several gotchas. No formula evaluate for a start.
255 char limit for range names, thus Lambdas limited to that.
No PowerQuery, recently something has arrived but not sure it is complete.
No ODBC driver built in, I have to buy one and it has limitations.
etc
Tables can give unexpected results to the uninitiated (i.e me five weeks ago).
I love watching someone else use Excel, I always learn new tricks. Never fails. 

Anthony