by David Hager
Just for fun: What is the hottest pork BBQ sauce? Give answers in comments and I will give mine in a subsequent post.
Although I cannot read Microsoft’s mind, I am fairly certain that the LAMBDA function was created to convert complex and commonly used worksheet formulas into named formulas that can be used by a normal Excel user without having to copy/paste those large formulas. Since there are literally millions of successfully used formulas in Excel applications that cannot be accessed for this type of conversion until companies upgrade their Office versions (i.e. – 365), those in the Excel community either have to create new (and incredibly powerful) Lambdas or convert formulas that are globally available.
In this latter category, virtually all manner of charting data uses different types of moving averages. The most basic form of moving average is commonly referred to as the simple moving average. Briefly, in a column of data the first n rows can be averaged to afford a single data point, which would be the first point in the new series. For example, in A1:A10, the first point in column B would be AVERAGE (A1:A10) in cell B10. This formula can be copied down for subsequent points. However, the goal would be to have a single formula for ALL moving averages. If A1:A25 contained the data and an 8 point moving average was desired, the formula =SMA(arr,n) starting in B1 would return the desired result. Since formulas in B1:B7 would be trying to return a result with less than 8 data points, an error would occur and, for charting purposes, NA() would be returned for those cells. The LAMBDA formula that would fit these requirements is:
=LAMBDA(arr,n,LET(x,SCAN(0,arr,LAMBDA(x,y,x+1)),IF(x>=n,AVERAGE(INDEX(arr,x-n+1):INDEX(arr,x)),NA())))
The key part of this formula is SCAN(0,arr,LAMBDA(x,y,x+1)), which basically counts each row in the array. Thus, for every “event” that occurs, the value of x can be used for whatever mathematical operation is needed (in this case, AVERAGE).
Hopefully, there will be new LAMBDA blog posts coming soon!
Excel For You 