2

The documentation of the Google Sheets function arrayformula says

Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.

This is working well in the following case which simply divides all the values in column A by five:

=arrayformula(A2:A / 5)

Now I want to lower-bound the result in every row by 0, i.e. in row n I want to compute max(An, 0). This is my non-working attempt:

=arrayformula(max(A2:A / 5, 0))

The max itself would work perfectly without the use of arrayformula, i.e. if I fill max(A2 / 5, 0) down all rows I get exactly what I want. I want the max function to operate on two single values, the single value found in An and the constant 0. The reason why the arrayformula approach is not working is that the max "steals" the range, aggregating the entire range A2:A into a single number. Thus, arrayformula operates on only this number and entirely ignores the range A2:A, consequently producing the same value for all cells.

How do I make the arrayformula interpret the range A2:A, or rather, how do I prevent the max function from doing so and have it operate on two single values instead?

(I can solve the issue with an if function instead, but that does not seem particularly elegant and requires a lot of code repetition, in particular since my real problem uses more involved arithmetic and a combination of min and max.)

Simon Fischer
  • 1,154
  • 6
  • 22
  • What do you mean by "lower bound"? Please provide an example of a list of input values and a list of desired output. – daniel Feb 23 '16 at 18:37
  • By lower bound I mean the result should be at least 0. Hence the `max`. The division by 5 is just an example to provide something meaningful to arrayformula. Input=10 -> Output=2. Input=-10 -> Output = 0. – Simon Fischer Feb 23 '16 at 19:18
  • Please provide example that look like this: input = {1, 2, -3, 5, 10, 0, -1}, output = {1,2,5,10}. It looks like the function you want is `filter()` – daniel Feb 24 '16 at 00:47
  • Input={10, 5, 0, -5, -10} -> Output={2, 1, 0, 0, 0} – Simon Fischer Feb 24 '16 at 06:43
  • Please provide more clarification on what you want. Do you want the function to return one single value or an array? Your question is confusing because `max` and `min` both return single value, but you are asking about `arrayformula` ? Are you asking about optimization of your function, in which case you should really provide actual code? – daniel Feb 24 '16 at 08:48
  • I found similiar questions [here](http://stackoverflow.com/questions/29887002/using-min-inside-arrayformula) and [here](http://stackoverflow.com/questions/11094059/finding-the-maximum-of-minimum-values-google-spreadsheet-excel/35625654#35625654). Will it help you? – Max Makhrov Feb 25 '16 at 12:07

3 Answers3

1

MAX is a aggregate function: those functions perform a calculation on a set of values and return a single value. You may want to check if this works (assuming your values in col A)

=ArrayFormula(if(len(A2:A),--(A2:A/5>0)*(A2:A/5),))
JPV
  • 26,499
  • 4
  • 33
  • 48
  • Thanks, but the question already states that `max` is an aggregate function. The real problem is more involved and requires more nesting with `min` etc. So your solution will work for the SSSCE I posted but will not solve the general problem. – Simon Fischer Feb 23 '16 at 22:51
  • Ok. There is no way to see the 'general problem' if you don't show it. As Daniel already mentioned some clarification of the end goal would certainly help. – JPV Feb 24 '16 at 15:36
0

You have two options:

=arrayformula(if(A2:A>0,A2:A/5,0))

and

=arrayformula(filter(A2:A,A2:A>0)/5)

The first option will give you zero when there is a negative number. The second option will skip zeros, so you may end up with a shorter array.

daniel
  • 638
  • 4
  • 14
  • I appreciate your effort, @daniel, but as I said I can solve this with an if function easily, but my real problem is more involved in which a simple if won't work. I think it won't help if I make the question more complicated. Note that for the solution with `filter` you actually do not need the `arrayformula` in the first place. – Simon Fischer Feb 24 '16 at 07:59
0

With the (new in 2022) MAP() function and an appropriate LAMBDA() function, it looks as if you can create an arbitrarily complex array output.

In your example case,

=MAP(A1:A,LAMBDA(dataValue, MAX(dataValue/5,0)))

should do the job.

David Knapp
  • 33
  • 8