I'm trying to clamp the values in an array to some lower and upper bounds, before doing further operations on the resulting array. I can provide a fuller spreadsheet example as an attachment if necessary, but the following demonstrates the issue: =SUM(MAX({-10,10,20,30},0)) What I'd like to achieve, is for MAX() to be applied to each array element in turn, resulting in an array with values {0,10,20,30}, with SUM() returning the value 60.
MAX() is presumably acting as intended, and I'm not seeing anything in the built-in functions to support this behavior. So, unless I'm missing something, I guess this bug report is to request something like an MAPPLY() function, which would allow a non-array supporting function to be passed in by name (perhaps as a string) along with an array, and would apply the function to each array element individually. Or alternatively a whole bunch of MMAX, MMIN, etc. functions.
=SUM(IF({-10;10;20;30}>0;{-10;10;20;30})) close with ctrl+shift+enter
Thank you for the suggestion. Unfortunately it suffers from two issues: First, repeating the expression that creates the array violates the DRY principal, which makes it error prone, verbose and more difficult to write, read or edit. In particular, consider the consequence of repeating the expression that generates the array if it is complicated and contains multiple nested function calls. Secand, it only solves my overly simplistic example because the example clamps to zero. Even a slightly more general case of clamping, say, clamping below 25, doesn't work with this approach.
But let's not get too off in the weeds trying to solve a specific example. Please treat this bug report as an enhancement request to add new capabilities as described in the bug summary. And please treat comment 0 and comment 1 as contributing just one specific usecase that would benefit from the requested enhancement, not as a specific problem to solve for me personally.
Let me be more specific about my request for enhancement, at least as a starting point. How about adding a new function, "MAPPLY", to apply another function to the individual elements of a list/matrix and return a list/matrix of the same dimensions. Syntax: MAPPLY(Array; FunctionName; Arg 1 [; Arg 2;][...;[Arg 255]]) "FunctionName" is the name of a function, as a string. The Arg arguments are either a value, or else the special string "*". Just before FunctionName is applied to the value of an element of the array/matrix, the special string "*" is converted to the value of that element. FunctionName is then be invoked with the Arg arguments passed in the order in which they are specified to MAPPLY.
A formula like =SUMPRODUCT({-10;10;20;30};CURRENT()>0) seems to work as you expect, looking simple. Don't expect a function like you want implemented, there are enough functions pending to develop for compatibility with Excel.
[OT] (In reply to m.a.riosv from comment #6) > =SUMPRODUCT({-10;10;20;30};CURRENT()>0) @Jonathan Watt, JIC, FYI, SUMPRODUCT() is already an array function that does not require CSE, so the formula that Miguel presented in comment 6 should be introduced with a simple [ENTER].
(In reply to m.a.riosv from comment #6) > A formula like > =SUMPRODUCT({-10;10;20;30};CURRENT()>0) > seems to work as you expect, looking simple. Gosh, I hadn't thought to use CURRENT in this scenario. Very interesting that that works. (I assume it creates an array of boolean values, but the Function Wizard just shows "Err:518" -- despite it working -- so I can't be sure.) > Don't expect a function like you want implemented, there are enough > functions pending to develop for compatibility with Excel. Missing Excel functionality does indeed seem like it would be higher priority. Thanks for the expectation setting. Please feel free to close this as WONTFIX or something if you think that this request will just be bug clutter with no real prospect of ever being implemented.
(In reply to ady from comment #7) > JIC, FYI, SUMPRODUCT() is already an array function that does not require > CSE, so the formula that Miguel presented in comment 6 should be introduced > with a simple [ENTER]. Thanks for the clarification.
(In reply to Jonathan Watt from comment #8) > Function Wizard just shows "Err:518" -- despite it working -- so I can't be > sure.) Err:518 means UnknownStackVariable, which would seem somewhat reasonable when there is still no CURRENT() value (in the F.W.). There are enhancement requests opened for years. There is no way to know beforehand _when_ someone will take care of it, if ever. Closing it as WF will only assure that it won't be implemented.
So you are after something like R's *apply() family (or purrr's map*() family), with recycling of arrays in case of mismatch in their length? Isn't MS Excel's MAP function close to that? https://support.microsoft.com/en-us/office/map-function-48006093-f97c-47c1-bfcc-749263bb1f01