Rack of Lambda
There’s been a lot of content shared recently to commemorate the upcoming 40th anniversary of Microsoft Excel. Personally, I’ve only been using Excel for around half that time, but October also marks my 2-year anniversary since "joining the conversation" on this forum. As a gift from me to you (anyone interested), I’ve spent some time over the past few weeks revisiting old posts, updating methods I shared previously and packaging them into a collection of generalized Lambda functions to assist with a variety of common array manipulation and transformation scenarios. The attached file contains some 35+ Lambda functions, ranging from very simple concepts to much more advanced techniques. You can also import them directly from my gist, if desired. While they were all compiled and composed of my own accord, I would be remiss if I failed to credit the community and its members as a major resource in my own development. The amount of knowledge, tips and tricks gained through community collaboration is simply invaluable. You may notice some recurring themes in the way I’ve written many of the functions. For example, I like to keep the optional arguments as simple as possible, using either Boolean values passed to IF, or numeric options from 0 to 3 passed to CHOOSE. Also, many of the array transformation functions use TOCOL-IF-SEQUENCE in one way or another, with MOD-QUOTIENT-SEQUENCE used only a few times in the more complex algorithms (e.g. HWRAP and VWRAP). The collection also includes a few examples of Lambda recursion, the most notable being PF (Prime Factorization). CROSSJOINM was written as a "how-to" demonstration for filtering multiple optional arguments using LAMBDA and NOT-ISOMITTED. There’re also some powerful scanning functions like SCAN3, which can handle multiple input arrays, as well as EVALS with VALS2 to VALS7, which can store and recall multiple variables at each iteration (useful for corkscrew calculations). What you won’t find, however, are methods that use INDEX in an iterative manner with functions like MAKEARRAY, SCAN, etc. as these are only efficient when iterating over a range reference (they will bog down considerably and become practically unusable after just a few thousand iterations when looping over an array object). As such, I don’t recommend them as "generalized" solutions, although they can be very effective on a case-by-case basis. Similarly, you will only find 2 examples in this collection that use REDUCE-STACK in a limited capacity, with fewer than 10 iterations, as I also consider this to be a method of last resort due to its problems with efficiency when the number of iterations cannot be controlled. Hopefully one or two of them proves useful. If not, no big deal. Many of the examples in the attached file are interactive, so you can see how the different options affect the output. For those brave enough, please feel free to share your own custom functions too. I’d love to see what you got. Cheers!
Profit/Loss per Day Calendar
Hi All, I am looking for formula for these 2 items. 1. Want total Profit/Loss per date in Calendar (E.g. Total Profit on 02 Feb 2022 was -90.65). 2. If Profit Cell to be Green, If Loss cell to be Red Link to Excel Sheet. https://1drv.ms/x/s!AlvQCyQGg78NgkNEsm2PYzAfJIEo?e=bF7ctc
Have multiple CONVERT cells update when any one cell's value is changed
I want to create an excel document that helps my team set correct parameters (Max feed rate, acceleration, etc...) for various CNC machine controllers that use different units of measurement. I know the formulas, and understand how CONVERT works, and can set it up so that, for example ## "/min in one cell converts to ##mm/s in another. What I would like to do is have this work so that when any of the cell values are changed all the rest of the related cells update accordingly based on the conversion applied to their cell. For example, depending on the machine and controller, I can have axis accelerations presented in four different units: mm/s^2 mm/min^2 in/s^2 in/min^2 I would like to be able to have all four of the above cells be populated with the correct acceleration value, and when any one of the four is updated the remaining three cells are all automatically updated to reflect this change. My initial research shows I will likely need to use Macros/VBA, but I have yet to come across an example use case similar to mine. I would appreciate knowing if this can be done, if I am on the right track with using macros, and any suggestions as to how it can be accomplished. If I can get it to work for the above example I feel I can take that experience and apply it to the other parameters. Thank you, Tom
Two lists of transactions. Want to create one large 'combined' one.
I have a workbook which I use for tracking transactions across two bank accounts. Each account has a separate 'ledger' sheet which lists all the incomings and outgoings. I want to create a third sheet which shows the entries of both accounts in one combined ledger. I would like it to be sorted by date and to have one column to indicate which account the entry is coming from. This is a link to a a dummy workbook with three sheets: Combined, Account 1 and Account 2 so you can see what I mean. https://1drv.ms/x/c/eea13e24843cdffd/EdNz2TLk0hJEueH-abYJF6ABxp69AS5eU0NqCOG4vlZAXQ?e=iYZ2Do If this is possible, please could someone with better skills than me please advise! Thanks!
