A generalised Lambda helper function that return arrays of arrays using bisection.
Specifically this required days of the week to be sorted by multiple price criteria. [This is intended as a topic for discussion as opposed to a specific request for help] I used the problem to develop a Lambda function that uses a binary tree to select the rows at the leaf nodes and perform a straightforward sort. The results then get stacked pairwise until the blue table of results is returned BYROWλ "Applies a user-defined function by row and collects array results into a stacked array" =LET(resultϑ, BYROW(array, ThunkFnλ), BinaryTreeλ(ROWS(resultϑ), "", Derefϑarrλ)) The main bisection recursion is hidden from the user BinaryTreeλ "Generates binary numbers stacking blocks pairwise to an upper limit" =LET( maxNode, BASE(nodeCount - 1, 2), maxLevel, LEN(maxNode), level, LEN(parentNode), maxChild, LEFT(maxNode, 1 + level), IF( level < maxLevel - 1, LET( childNode0, BinaryTreeλ(nodeCount, parentNode & 0, FNλ), childNode1, BinaryTreeλ(nodeCount, parentNode & 1, FNλ), IF((parentNode & 0) = maxChild, childNode0, VSTACK(childNode0, childNode1)) ), IF( (parentNode & 0) = maxChild, FNλ(parentNode & 0), VSTACK(FNλ(parentNode & 0), FNλ(parentNode & 1)) ) ) ) but it is intended to non-problem specific and hence reusable. A key feature is that the user provides a Lambda function that performs the required calculation as if BYROW would return the result, but the function is converted to one the returns a thunk and hence avoids the array of arrays problem. ThunkFnλ "Accepts a user-defined Lambda function that returns an array result and generates a related function that returns the corresponding thunk" =LAMBDA(arr, LAMBDA(userFnλ(arr))) Finally Derefϑarrλ "Dereferences a term from a thunk array using a zero-based binary pointer and expands the resulting scalar thunk" =INDEX(resultϑ, DECIMAL(ptr, 2) + 1, 1)() converts the binary node pointer to a decimal index. This is all crazily heavy but, hopefully, would be simple to reuse for other problems since the main processing is problem independent.Excel Community: Simplifying Spaces and Labels
Hi all, As you may have noticed already, we as the Excel Team have done a bit of "spring/summer cleaning" for the community. We have received feedback that the number of "spaces" was simply too many at nine, so we have pared things down. Now, there are three community spaces: Excel: this will be the primary place for posting content, as many of the old spaces have been migrated as labels will find a good home here BI and Data Analysis: this will be a good place for posting about tasks and questions that cut across Excel, Power BI, and other topics in this realm Resources and Community: this will increasingly become a place for folks to share sample files and templates with each other Q: What happened to the other spaces that used to exist? A: They have since been rolled up as "Labels", which you can find in the "More Resources" sidebar under "Labels" of any of the three spaces mentioned above. Look for these on the right side: Please let us know if you have any other questions. Thanks for reading!
Spin Control only increments
I have a sheet that I use for tracking statistics. Each cell contains a simple number with 0 decimal places. I have added a spin controls in my sheet to simply increment and decrement the value of a field by 1. As I have added and tested the spin controls, I have found that many of them will only increment the field, not decrement the value. Both the up and down buttons are visible, but when clicking the down arrow in the control, the field is incremented improperly. Thanks for the help
REMOVE A CHECKBOX FROM EXCEL WORKSHEET
I was handed an Excel worksheet that has three checkboxes that I wish to remove but cannot seem to remove them. Excel Help says to right click the Box and then hit Delete. There is no Delete in the ensuing dropdown menu and Delete on the keyboard does not work. Any suggestions?Would a FOR.EACH function be useful in Excel 365?
The attached files are based upon a workbook that emulates the proposed function using a VBA macro. There would appear to be many potential applications in situations where the @operator needs to be used to restrict the operation of an array parameter.What do you think of thunks?
OK, so the most likely response by far is going to be "I don't". However, I tried one of Omid Motamedisedeh's regular challenges and found it a suitable problem for exploring some of the lesser known byways of modern Excel. The challenge is to pick out locally maximum values from a rolling range. What I did was to write a function that used MAP to select one cell at a time, using DROP to remove the initial cells and TAKE to return a range of 5 cells with the active cell in the middle. The direct route to solving the stated problem would be to calculate the maximum value within each range immediately, but I was interested in the more general problem of "could I return the array of ranges in a form that would support further analysis?" As shown, the following formula ROLLINGRANGEλ = LAMBDA(values, n, LET( rows, SEQUENCE(ROWS(values)), MAP(rows, LAMBDA(k, LET( m, QUOTIENT(n, 2), rng, TAKE(DROP(values, k - m - 1), MIN(k + m, n)), rng ) ) ) ) ); gives and array of ranges error, but simply by enclosing the 'rng' variable within a further LAMBDA ... LET( m, QUOTIENT(n, 2), rng, TAKE(DROP(values, k - m - 1), MIN(k + m, n)), LAMBDA(rng) ) will cause Excel to return an array of functions, each one of which would return a range if evaluated. In the attached workbook, a number of formulae are based upon this array of functions = ROLLINGRANGEλ(dataValues, 5) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, ROWS(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, ISREF(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, AVERAGE(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), dataValues, LAMBDA(ϑ,v, MAX(ϑ()))) = LET( rollingMax, MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, MAX(ϑ()))), FILTER(Data, rollingMax=dataValues) ) The first simply returns #CALC! errors on the worksheet which is the normal response to a Lambda function defined on the grid. The second formulas uses ROWS to show that the ranges are not all the same size, the third shows the returned objects to be range references and not simply arrays of numbers, the forth is a rolling average while the fifth is a rolling MAX. The final formula returns the solution to the problem, being a filtered list. The purpose of this post is to demonstrate that Excel, which started out as a 'simple' spreadsheet program, now contains a very different programming environment that shares a function library and uses the grid for input/output but, other than that, has very little in common with 'normal' spreadsheet practice! A related survey can be found at https://www.linkedin.com/feed/update/urn:li:activity:7285432559902068736/Please update Excel to handle more than 15 digit numbers!
I'm a professional database designer, and this limitation has caused me no end of headaches. The problem: numbers that are more than 15 digits long have all digits after the 15th converted into zeros. Microsoft provided workaround: format the the field as text. This workaround is only useful if you are doing data entry directly into an existing Excel spreadsheet that you are able to format the cells ahead of time. It's incredibly unhelpful when you are pushing and pulling data from different data sources. For example, if I need to push data out of a program like FileMaker to Excel, and the system I am pushing from has numeric fields (in particular ID fields), excel sees them as numbers and replaces the digits after the 15th. This jacks up formulas as well as any data synching possibilities. In situations like that, I end up having to export the data as .csv file, and then pull it in to an excel sheet and then do a bunch of conversion options on import (which does not always work, btw). But this is not a practical solution for every day users who are not tech savvy. I should be able to export the data directly to excel without the data being converted. This has been a big problem for every client I have that requires data being pushed to Excel. And this problem has existed for decades. Other spreadsheet programs (like google sheets) do not appear to have this issue. Can someone please explain why Excel continues to cling to this archaic standard? Are there any plans to update Excel to handle more than 15 digits? I know I am not the only one who has run into this problem. I've seen all kinds of posts about it. But trying to let Microsoft know how much of a problem this is has been a challenge. Their article on the subject had a link to give product feedback, and that link led me to this space. So here it is: product feedback for Excel. PLEASE FIX THIS! Thank you. Chris
Scroll Bar not showing arrows
I noticed something new and can't seem to fix it. I've added a scroll bar (form control, NOT Active X). For some strange reason the little arrows that are usually part of a scroll bar are not visible. Is this new or is there a way to get them to show? I've tried widening and resizing the scroll bar but nothing works. The scroll itself works fine, but visually it's missing the arrows (if that makes sense?) Thanks.
Automator/AppleScript and Excel 16.25 Script issue in Mac OS
I'm having issues getting Automator/Applescript to work with excel 16.25. I customized the AppleScript from (https://discussions.apple.com/thread/5528059) to search for files containing an ISBN13 string (no dashes) in column B of an Excel worksheet with a wildcard search at the beginning and end of the file name. I'm basically searching for a filename that has the ISBN13 in it in part of the filename. Search Directory is usually on a server mounted to the Mac. This has not changed. Destination is usually the user’s desktop. When testing, it doesn’t matter I use the desktop or a folder not he root level of my hard drive. The script used to run just great in the MacOS in various versions all the way up to and including MacOS 10.13 and in Excel versions up to Office 2016. Now in MacOS 10.14 and Microsoft 16.25 it no longer works (I think it is an Excel issue as it does not work inExcel 16.25 in earlier versions of the MacOS). It just runs without any errors, but it does not copy the file. I can run the commands in terminal (pasting int he search and destination path), and everything works just great (it finds the file and copies the file), so something tells me it’s something with the OS or Excel. I have tried running it both as a Workflow and as a standalone app. When I run it as an app, I have granted it permission to control Microsoft Excel in Security and Privacy / Automation and allowed the saved AppleScript App to control Excel. But, it still does not work. Thoughts? What am I missing? on run {input, parameters} set theDirectory to quoted form of POSIX path of (choose folder with prompt "Select Search Directory") set theDestination to quoted form of POSIX path of (choose folder with prompt "Select Directory to Copy Files") tell application "Microsoft Excel" tell active sheet tell used range set rc to count of rows end tell set theList to get value of range ("B1:B" & rc) as list repeat with theItem in theList if contents of theItem is not {""} then do shell script "find " & theDirectory & " -iname '*" & theItem & "*' -exec cp {} " & theDestination & " \\;" end if end repeat end tell end tell return input end run
