# N-1 factorial arrayformula in Google Sheets?
**URL:** https://community.glideapps.com/t/n-1-factorial-arrayformula-in-google-sheets/22579
**Category:** Ask for Help
**Created:** [February 10, 2021, 7:38pm UTC](https://community.glideapps.com/t/n-1-factorial-arrayformula-in-google-sheets/22579 "2021-02-10T19:38:15Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@belzoni](https://community.glideapps.com/u/belzoni)
#### Post date: [February 10, 2021, 7:38pm UTC](https://community.glideapps.com/t/n-1-factorial-arrayformula-in-google-sheets/22579/1 "2021-02-10T19:38:15Z")
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.
---
### Author:  [@AymenM](https://community.glideapps.com/u/AymenM)
#### Post date: [February 10, 2021, 7:41pm UTC](https://community.glideapps.com/t/n-1-factorial-arrayformula-in-google-sheets/22579/2 "2021-02-10T19:41:41Z")
Hey !
Thank you for raising this question, you may find your solution here :
[https://support.google.com/docs/table/25273?hl=En](https://support.google.com/docs/table/25273?hl=En)
Hope this helps !
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [February 10, 2021, 7:42pm UTC](https://community.glideapps.com/t/n-1-factorial-arrayformula-in-google-sheets/22579/3 "2021-02-10T19:42:15Z")
So the number in the ‘given’ column could potentially be unknown, or any number? Is there an upper or lower limit?
---
### Author:  [@belzoni](https://community.glideapps.com/u/belzoni)
#### Post date: [February 10, 2021, 7:49pm UTC](https://community.glideapps.com/t/n-1-factorial-arrayformula-in-google-sheets/22579/4 "2021-02-10T19:49:43Z")
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.
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [February 10, 2021, 8:05pm UTC](https://community.glideapps.com/t/n-1-factorial-arrayformula-in-google-sheets/22579/5 "2021-02-10T20:05:33Z")
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.
---
### Author:  [@belzoni](https://community.glideapps.com/u/belzoni)
#### Post date: [February 10, 2021, 8:14pm UTC](https://community.glideapps.com/t/n-1-factorial-arrayformula-in-google-sheets/22579/6 "2021-02-10T20:14:43Z")
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!
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [February 10, 2021, 8:29pm UTC](https://community.glideapps.com/t/n-1-factorial-arrayformula-in-google-sheets/22579/7 "2021-02-10T20:29:13Z")
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.
---
### Author:  [@NoCodeAndy](https://community.glideapps.com/u/NoCodeAndy)
#### Post date: [January 17, 2024, 4:53pm UTC](https://community.glideapps.com/t/n-1-factorial-arrayformula-in-google-sheets/22579/8 "2024-01-17T16:53:27Z")