I had posted Tips & Tricks 119 – Numerology Sum of the Digits aka Sum the Digits till the result is a single digit. In this, I had explored how to add a number and arrive at a single digit. For example, if you have to add 8 + 7 the answer would be 15. You need to further add up 1 and 5 of 15 and final answer would be 6. And this is numerological sum.
In numerology, we calculate the digits corresponding to a name. All alphabets carry a number corresponding to 1 to 9. A has 1, B has 2……I has 9, J has 1…R has 9 , S is 1…Z is 8 as illustrated in the table below.
Hence, if my name is Vijay, then I need to add 4 + 9 + 1 + 1 + 7 = 22 = 2+2 = 4
Hence, if a person's name is Julia Richards, then following will be numerological sum = 1 + 3 + 3 + 9 + 1 (Corresponding to Julia) + 9 + 9 + 3 + 8 + 1 + 9 + 4 + 1 (Corresponding to Richards) = 61 = 6 + 1 = 7
Challenge before you is to find a formula which calculates Numerological Sum for a given name if name is given in cell A1.
The solution to this problem will be published after a month i.e. on 02-Aug-16.
=MOD(SUMPRODUCT(LOOKUP(MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1),{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"},{1;2;3;4;5;6;7;8;9;1;2;3;4;5;6;7;8;9;1;2;3;4;5;6;7;8}))-1,9)+1
My array formula is as below.
=SUMPRODUCT(IFERROR(VALUE(IFERROR(MID(SUMPRODUCT(IFERROR(VALUE(IFERROR(MID(SUMPRODUCT(MOD(IFERROR(CODE(MID(LOWER(SUBSTITUTE(A1," ","")),ROW(A1:A50),1)),0)-97,9)+1,IF(ROW(A1:A50)<=LEN(LOWER(SUBSTITUTE(A1," ",""))),1,0)),COLUMN(A1:C1),1),0)),0)),COLUMN(A1:C1),1),0)),0))
Vijaykumar Shetye, Goa, India
Thanks a lot for the formula.
However in numerology you need to count vowels and consonants apart, so I have modified Vijay's formula a little bit – I made a 3 different ones and added an IF to show 0 if the field is blank:
VOLVELS:
=IF(A1=0,0,MOD(SUMPRODUCT(LOOKUP(MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1),{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"},{1;0;0;0;5;0;0;0;9;0;0;0;0;0;6;0;0;0;0;0;3;0;0;0;7;0}))-1,9)+1)
CONSONANTS:
=IF(A1=0,0,MOD(SUMPRODUCT(LOOKUP(MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1),{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"},{0;2;3;4;0;6;7;8;0;1;2;3;4;5;0;7;8;9;1;2;0;4;5;6;0;8}))-1,9)+1)
TOTAL:
=IF(A1=0,0,MOD(SUMPRODUCT(LOOKUP(MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1),{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"},{1;2;3;4;5;6;7;8;9;1;2;3;4;5;6;7;8;9;1;2;3;4;5;6;7;8}))-1,9)+1)
Now if you would like to count Y as a consonant, as some numerology schools does, it goes as follows:
VOVELS:
=IF(A1=0,0,MOD(SUMPRODUCT(LOOKUP(MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1),{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"},{1;0;0;0;5;0;0;0;9;0;0;0;0;0;6;0;0;0;0;0;3;0;0;0;0;0}))-1,9)+1)
CONSONANTS:
=IF(A1=0,0,MOD(SUMPRODUCT(LOOKUP(MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1),{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"},{0;2;3;4;0;6;7;8;0;1;2;3;4;5;0;7;8;9;1;2;0;4;5;6;7;8}))-1,9)+1)
Absolute genius! Truly incredible lines of code! I have searched the internet for these lines – so glad that you had the brains for this!
"However in numerology you need to count vowels and consonants apart" since when? Numerology does not take into account upper or lower case letters. so you are barking up the wrong tree.