72. Numerology Sum of the Digits aka Sum the Digits till the result is a single digit
In Numerology, it is often a task to add the digits till the result is a single digit. For example,
74 = 7 + 4 = 11 = 1 + 1 = 2
23 = 2 + 3 = 5
78 = 7 + 8 = 15 = 1 + 5 = 6
1234567 = 1 + 2 + 3 + 4 + 5 + 6 + 7 = 28 = 2+ 8 = 10 = 1+ 0 = 1
The formula to achieve the same is
=MOD(A1-1,9)+1
73. Generate Sequential Numbers and Repeat them
Suppose, you have been given the task to generate a sequence of numbers and repeat them.
For example -
1,2,3,4,1,2,3,4,1,2,3,4
You can use the below formula and drag down -
=MOD(ROWS($1:1)-1,4)+1
Replace 4 with with any other number to generate any other sequence. Hence, if you want
to generate 1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10 then formula
becomes -
=MOD(ROWS($1:1)-1,10)+1
The structure of the formula is
=MOD(ROWS($1:1)-1,X)+Y
X - Number of numbers
Y - Starting Number
Utilizing above formula, you want to generate the sequence
5,6,7,8,9,10,5,6,7,8,9,10,5,6,7,8,9,10, then use below formula (You need 6 numbers and
stating number is 5)
=MOD(ROWS($1:1)-1,6)+5
74. Repeat a Number and Increment and Repeat....
Suppose, you have been given the task of repeating a number and increment that number
and repeat it. For example -
