Skip to content

Instantly share code, notes, and snippets.

@paxperscientiam
Last active February 15, 2022 05:11
Show Gist options
  • Save paxperscientiam/3c447028d53e4ac9048d02a73732afb6 to your computer and use it in GitHub Desktop.
Save paxperscientiam/3c447028d53e4ac9048d02a73732afb6 to your computer and use it in GitHub Desktop.
Useful Excel WS formulae
' Useful Excel Worksheet formulas
' This is a living paste...stay tuned for more
' Unless otherwise state, assume Excel Table structured reference syntax.
' Whole Number Sequence
= [@counter] = 1+ROW()-ROW([counter])
' Fixed-Divisor Modulo Numeric Cycle
' Reference: http://www.bennadel.com/blog/2240-creating-repeated-sequences-with-the-modulus-mod-operator.htm
= [@MNC] = MOD([@counter]-1,N)+1, [counter] = 1,2,3,4,5,6... => {1,2,..,N,1,2,..,N,...}; whereby "N" is your divisor
' Fixed-Divisor Modulo Alpha Cycle
' "[@counter]" refers to a column containing a whole number sequence beginning with 1
= [@MAC] = CHAR(96+1+MOD([@counter]-1,26))
' Dynamic Modulation of Sequential Numeric Sequence
' Much thanks to /u/excelevator of Reddit for their invaluable input
' [counter] shall refer to whole number sequence
' [marker] shall refer to a column that uses some marker, say "1", to indicate that the sequence should "reset"
' Option One
= [@mod] = IF(OR([@counter]=1,[@marker]=1),1,IF(ISNUMBER(OFFSET([@mod],-1,0)),1+OFFSET([@mod],-1,0),1))
' Option Two
= [@mod] = --([@marker]=1)+([@marker]<>1)*IF(ISNUMBER(OFFSET([@mod],-1,0)),1+OFFSET([@mod],-1,0),1)
' Option Three
= [@mod] = CHOOSE(1+ISNUMBER(OFFSET([@mod],-1,0))*(1+[@marker]=1),1,1+OFFSET([@mod],-1,0))
' Option Four -- my fave
= [@mod] = --([@marker]=1)+([@marker]<>1)*(SUM(1,OFFSET([@mod],-1,0)))
' Option Five -- most compact
= [@mod] = IFERROR(IF([@marker]=1,1,OFFSET([@mod],-1,0)+1),1)
' Dynamic reference that begins with first row of some column and ends at row of computation (Excel Table structured reference syntax)
' Useful in combination with COUNTIF()
= OFFSET(Table1[[#Headers],[column1]],1,0,1+ROW()-ROW([column1]))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment