Since LAMBDA and LET and AFE came out I've enjoyed spending time developing new functions. For years I had been stretching the limits of what I could do with names and tables, with selective forays into VBA. This new custom function platform was made for people like me who were not fully served by either Excel alone or Excel with VBA. I've since created dozens of functions including some that I think are pretty cool tools.
But what's the point? Tools are not an end in themselves, even though my shopping behavior (and blog posts) might suggest so. I buy tools with at least a vague vision of their use. Sometimes the tool finds immediate use, other times it sits on the bench, just in case.
Shopping for tools can lead to invention, when the right one is not to be found, or requires a jig to fit the purpose. Building tools feels purposeful, but they're still just tools. When I find myself following a white rabbit, I snap back to the vision of the result, which alone will justify the many unsung tools it employs. Better to be under construction for as long as it takes than to succumb to technological self-satisfaction.
Tools plus skills result in capability, capability plus purposeful action result in achievement. xlegant is about concrete, relevant application of tools by using them intentionally towards a purpose, with integrity, transparency and elegance. So please bear with me when I focus on basic tools and skills, and catch the vision of where they can lead you.
Upcoming Projects
Now that I have extended date functions and can use arrays efficiently, I will revisit and improve the Family History Timeline that I introduced in April. To recap, it's designed to give a graphical representation of the lifespan of individuals against a customizable time scale and a background of historical events. The data is presumed to come from a genealogical database, with a mix of text and numeric dates. The calculations to identify bloodlines and dates and conditional formats are extensive. I expect that several of my newest tools and skills will be useful in pursuing a robust and elegant product.
Another project I'm consulting on is a Cumulative Schedule Performance Measure. Schedule performance is used in many industries, expressed as the percent of deliveries received on-time within a tolerance window. When each order is uniquely identified, the calculation is simple, by comparing receipt date and quantity to the order. Some companies use a "blanket order" or "scheduling agreement," with a single order number for many deliveries, which receipts fulfill cumulatively. In this scenario, a different calculation is required to compare cumulative receipts to cumulative orders. A reliable and useful measure has been elusive for years, and a new approach is needed.
A third project still in early conception is a nutrition tool. Excel has a food data type that has the potential to enter foods and amounts and return nutrition facts. This will be useful to manage a diet, especially for those who don't follow the FDA guidelines on recommended daily allowances. Excel will enable definition of personal targets, tracking, and finding which foods help or hurt.
These projects and others will appear in the coming weeks and months. If you have any needs or suggestions, please leave a comment or contact me.
Now, for today I have some more interesting LAMBDA functions, and a fix and upgrade of the d.calendar function, which is part of the date function package presented last week.
New Functions
m.rowsum: cumulative sum or net sum of a vertical array
m.rowdelta: delta of each row from the previous row of a vertical array
a.make: output array of row and column indexes
a.shift: shift an array up and/or left without changing the size
a.split: output array of the individual characters in a cell or array
f.foundin: Boolean true if a lookup value is found in an array
Updated d.calendar
New features, renamed and reordered arguments, improved code, and bug fix.
Comments