Anyone have a clever way to do write an array formula that reduces a number (N) across a N number of cells? Kind of like a factorial, but without the multiplication. I’d like to end up with the following table:
Given a value in Column A, please iterate N-1 across columns.
Hey !
Thank you for raising this question, you may find your solution here :
https://support.google.com/docs/table/25273?hl=En
Hope this helps !
So the number in the ‘given’ column could potentially be unknown, or any number? Is there an upper or lower limit?
There are practical limits to the Given… let’s call it 20.
I can write an equation in B column, then write a series of Ifs and drag them across. But I’ll need to play with it because I’m getting 0’s and -1’s, which are unwanted. I’m sure I can nest and tweak to succeed - just though there may a more elegant/clever way.
Off the top of my head, I would probably do the same thing with IFs and dragging the formula across. There might be some more elegant functionality, but I’m only familiar with common sheet functions.
If I wanted to keep it within glide, I would probably have a prebuilt table with 20 row numbered 1 through 20, then build out the arrays manually. That way you could create a relation/lookup to that sheet and pull back a corresponding array.
If I come up with something more creative, I’ll let you know.
Thanks, Jeff. This will do it then:
=if(A2-1<1,"",A2-1) then Drag/Fill to the block.
Column is filled by a Query indexing another sheet, so the number of rows is dynamic. So, I’ll just pre-pack the sheet (consuming valuable rows!) and move on. My original question wouldn’t have addressed need to fill those Rows, but I was hoping for something clever that might.
Thanks!
That should be reasonable as long as you wrap your formula in an arrayformula. Then you only need it once per column and you don’t have to prepack the entire sheet to account for dynamic rows.
As a side note, glide does not count rows that appear empty, even though they may have a formula in them. It won’t touch them when writing new data, but it shouldn’t count against your row count.
