Yes, it was inevitable that I take this cool tool to the limit. If for no other reason, it is a great bridge, like training wheels for the non-coder. With a little guidance, it can ease you into the proper programming mindset. As I’ve often emphasized, that starts with good purpose, to ensure that the effect on communication is net positive. I claim a little extra leeway because my purpose is to help you by demonstrating methods while trying to present examples that might actually be useful.
You can download text files of my functions here,
and paste them into AFE to try them out (see readme.txt). Make sure that you synch AFE with Name Manager frequently as you work on names.
Name Manager Limitation
Breaking news: below you'll read that I have not yet found the limit of how far LAMBDA will go. I have a late update on that. The f.shapes function is very long. I encountered no obstacles in synching it or using it in the sheet, but when I try to open it in Name Manager, it errs. This caused me to check the formula length and the maximum length allowed in Name Manager.
From my testing, the maximum length of a formula in Name Manager is 2084 characters. The function f.shapes was written in AFE, where it has 2505 characters. Somehow it works, even though it cannot be opened in Name Manager and therefore cannot be commented.
The function has almost 1000 spaces in it, which is in part a feature of AFE formatting. If most spaces were trimmed it would fit within the Name Manager limit. In any case, I think I will not try to go any longer, and will heretofore live within the limit that allows me to add Name Manager comments.
Namespaces
I previously mentioned the prospect of using namespaces to help catalog functions by prefixing names. This makes it unnecessary to sort names by comment, which doesn’t help in AFE anyway. I started with “my” as a default namespace, clearly differentiating my custom functions and making them easy to find by type-ahead. This I use for all general-purpose LAMBDA functions. I use “a” namespace for array constants, which come in quite handy as inputs for certain functions, and “f” for standard mathematical formulas.
I don’t know if this list will last, or if I’ll add more, or end up consolidating everything into “my”. The important thing is to have the simplest naming scheme that makes life easy for you and is transparent enough to be easily interpreted by others you share functions with. One of the best features of AFE is namespace management in Editor. Rename, or create new and cut/paste functions en masse.
A note about namespaces that is not obvious is that if you use any recursion in a LAMBDA function, the recursive instance of the name within the function must have the namespace prefix as well. This is easier to spot in Name Manager where all names are full than in AFE, where the namespace is at the top of the pane. AFE will grow on you as the tool to allow complexity to grow naturally. Making sure to prefix function references when the function name itself does not appear to be prefixed is the point to learn.
my.loan
If you’re not a finance geek but like me you’ve played around with loan scenarios, you know that the native functions are not all that easy to use. The first obstacles are usually finding the right function, then getting the terms lined up for the rate and the number of periods. If you’re mainly interested in analyzing the total cost over the life of the loan, you must step through all the arguments to get there. It’s not too bad, but one way to think of using LAMBDA is to make native functions more friendly.
In my.loan, I chose to limit the purpose to lifetime analysis of a loan for which the rate input is annual and the payment period is monthly. Required arguments are reduced from six to three and given common names. There are also three optional arguments, beginning with [output] which enables three other outputs in addition to the default; [compound] invokes the EFFECT function for monthly compounding; [begin] takes the place of the “type” argument in CUMIPMT.
The rate is input as APR, which is the way loan rates are normally expressed. I opted to make this input as if followed by “%” rather than as a decimal. For example, 6% APR is entered as 6. This is then divided by 1200 to convert to monthly rate. If I were referencing a column of percent-formatted APRs, I would divide by 12 instead of 1200.
The four outputs begin with CUMIPMT to get the total interest. Second, add the “amount” input (total principal) to get total loan payments, which is the default output. Third is the ratio of interest to principal, and fourth is the monthly payment. There is no need for CUMPRINC or PMT because they are easily derived from the simplified input parameters.
my.convert
Native CONVERT gives you too many units to pick from for the “from” unit. The “to” unit is then filtered to compatible ones. This my.convert function doesn’t alter that and doesn’t even give you the unit lists (recall that was on my wish list). What it does add is sentence output, like “1 kg = 2.2 lbm”. This is irrelevant for some, but for those who do occasional conversions it is a convenient reminder of both units. Optionally, by entering [output] = 1 (or any non-zero), the result is the naked converted number.
my.cell
Native CELL gives a picklist of cell attributes, with optional reference cell. Most of the attributes are not of much use. I picked two that I think might sometimes come in handy, format and width. Format, which native CELL returns as a code, is converted to a word. Width is returned by native CELL as a two-cell horizontal array, the first cell containing the width in characters and the second cell containing TRUE=default or FALSE=custom width. This is converted into a string with the column number, the width and “def” or “cus”.
If [ref] is provided as a cell address, the attribute of that cell applies. If [ref] is omitted, the attribute of the active cell applies. This mimics native CELL. Format is the default output, unless [output] is non-zero.
f.shapes
This was in part a test to see how far I could go. I did not find the limit yet. This combines sixteen geometry formulas into one function. The trick to make this possible is careful definition of the arguments, logical enough to pick up quickly if not remember. They are given long names for clarity, and LET aliases for brevity in processing.
The first three arguments define which formula is to be applied. First, “shape_dimensions” is either 2 (circle, rectangle, etc.) or 3 (sphere, cone, etc.). If “shape_dimensions” is 2 then “measure_dimensions” can be 1 (perimeter) or 2 (area). If “shape_dimensions” is 3 then “measure_dimensions” can be 2 (surface) or 3 (volume). Within each of these groups is a short list of “shape_type” values, generally sorted by number of inputs.
This is another case that would be greatly enhanced by array arguments to pick from. In lieu of that, the next best option I think is to keep arguments numeric, with instructions within the LAMBDA as well as in Name Manager comments.
The next three arguments are inputs to the selected formula: par_1 is required, such as radius or the first side of the shape; par_2 and par_3 are technically optional, but really conditional. They’re required if the selected formula requires them. This implies error management. There is unfortunately no easy way to inform the user which parameter is radius, or height, etc., this is something I'll have to work on documenting.
In the LET “value” segment is each of the sixteen formulas, each with its own error handling, as follows:
1. IF(par_2 + par_3, “err”, formula), where neither par_2 nor par_3 should be filled.
2. IF(par_2 * par_3, formula, “err”), where both par_2 and par_3 should be filled.
3. IF(AND(par_2, par_3=0), formula, “err”), where par_2 but not par_3 should be filled.
The final argument [sentence] builds a sentence from the inputs and output, as shown.
Comments