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.)

Improve this question
6
  • What do you mean by "lower bound"? Please provide an example of a list of input values and a list of desired output. Commented Feb 23, 2016 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. Commented Feb 23, 2016 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() Commented Feb 24, 2016 at 0:47
  • Input={10, 5, 0, -5, -10} -> Output={2, 1, 0, 0, 0} Commented Feb 24, 2016 at 6: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? Commented Feb 24, 2016 at 8:48

3 Answers 3

Reset to default
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),))
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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.
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.
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.

Improve this answer

1 Comment

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.
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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.