0

The Example sheet

The formula:

For all x from 1 to 10, if (Sell <= Buy * x) then x, else ">=10"

I thought I might be able to cheat a little with the sequence function:

=ARRAYFORMULA(MIN(IF(Sell range <= SEQUENCE(4,1,1) * Buy, SEQUENCE(4,1,1))))

But since the sell range is in the min function, I only get the first result.

Is there a good way to make this formula expandable? It's not necessary, I'm just really curious.

Improve this question
3
  • Is your goal to come up with an efficient/concise formula, or is it to learn (i.e., how to apply MIN with SEQUENCE)?
    – Erik Tyler
    May 22 at 16:27
  • @ErikTyler Both actually. It just bothered me that I didn't know of a way to take better advantage of functions and was relying heavily on IF.
    – ZythiQ
    May 23 at 7:45
  • There are ways to get the minimum applicable result from a sequence (or set of columns) with array formulas. But they are more complex a solution than your current case demands. If you're interested in seeing some for study purposes, let me know, and I'll share a few.
    – Erik Tyler
    May 23 at 18:59

1 Answer 1

Reset to default
0 
=ARRAYFORMULA(IF(ISBLANK(B2:B),"",IF(B2:B >= $A$2,CEILING(B2:B/A2),1)))

I added the function to your google sheet too. I couldn't work out a better logic so that it won't show a value if the cell is blank, so this will have to do.

It just divides the sell value to the buy value then using the ceiling function, rounds up. If the value is smaller than the buy value, then it will just return 1. I couldn't make use of the MAX function-- I was running to the same problem you were with the MIN function.

Please someone do suggest a better way of only including cells that has something in it. My brain feels like it's trying to reach for a solution but can barely grasp it.

Improve this answer
1
  • That's a really interesting approach! I suppose I should have done some fiddling to see if there was a pattern to exploit, thanks a bunch for the answer.
    – ZythiQ
    May 23 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.