Useful Excel WS formulae

Useful Excel WS formulae.txt

' 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]))