Tue 02 Aug 2016

Solution - Challenge 46 – Compute Numerological Sum for a Name

By |Tuesday, August 02nd, 2016|Categories: Solutions|Tags: , , , , , , , , |3 Comments

Below is a possible solution to the challenge – Challenge 46 – Compute Numerological Sum for a Name

The formula to calculate Numerological Sum for a Name would be -

=MOD(SUMPRODUCT(MOD(CODE(MID(SUBSTITUTE(LOWER(A1)," ",""),
ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1))+2,9)+1)-1,9)+1

Sat 02 Jul 2016

Challenge 46 - Compute Numerological Sum for a Name

By |Saturday, July 02nd, 2016|Categories: Challenges|Tags: , , , , , , , , |5 Comments

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.

1

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.

Tue 16 Feb 2016

Solution - Challenge 34 – Increment All Digits by 7 and Perform Numerological Sum of the Digits

By |Tuesday, February 16th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 34 – Increment All Digits by 7 and Perform Numerological Sum of the Digits.

=SUMPRODUCT((MOD((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+
7*ROW(INDIRECT("1:"&LEN(A1)))^0)-1,9)+1)*10^(LEN(A1)
-ROW(INDIRECT("1:"&LEN(A1)))))

The workbook containing the above solution can be downloaded from Solution - Challenge 34 – Increment All Digits by 7 and Perform Numerological Sum of the Digits.

Sat 16 Jan 2016

Challenge 34 - Increment All Digits by 7 and Perform Numerological Sum of the Digits

By |Saturday, January 16th, 2016|Categories: Challenges|Tags: , , , , , , |2 Comments

I had posted a relatively simpler problem Challenge 31 – Increment All Digits by 1 which talked about adding 1 to all digits i.e. if your number is 7409, you needed to add 7409+1111 and come up with the answer of 8520.

I had also 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.

Now, the challenge before you is to combine both the approaches and come up with the answer. Hence, if you have been given a number and you have to add those many 7's and you have to perform Numerological Sum on individual digits.

Example

1. Given number is 356 in cell A1, hence you need to add 777 to it. 356+777 = (3+7) (5+7) (6+7) = (10) (12) (13) = (1+0) (1+2) (1+3) = 134

2. Given number is 4072 in cell A1, hence you need to add 7777 to it. 4072 + 7777 = (4+7) (0+7) (7+7) (2+7) = (11) (7) (14) (9) = (1+1) (7) (1+4) (9) = 2759

Come up with a formula to do this (you can also do it in VBA, if you wish though formula is preferable). It should be able to do it for any number of digits. Excel can store numbers with 15 significant digits.

The solution to this problem will be published after a month i.e. on 16-Feb-16.

Sat 06 Sep 2014

Tips & Tricks 48 - Multi Column VLOOKUP

By |Saturday, September 06th, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

You know VLOOKUP, one of the most loved function of Excel. The syntax is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Here look_value can be a single value not multiple values.

Now, you are having a situation where you want to do vlookup with more than 1 values. For the purpose of illustrating the concept, let's say we have 2 values to be looked up.

Below is your lookup table and you want to look up for Emp - H and Gender - F for Age.

1

=INDEX(C2:C12,MATCH(1,INDEX(--((A2:A12=F2)*(B2:B12=G2)*(ROW(A2:A12)-ROW(A2)+1)<>0),,),0))

Concatenation Approach

=INDEX(C2:C10,MATCH(F2&"@@@"&G2,INDEX(A2:A10&"@@@"&B2:B10,,),0))

@@@ can be replaced by any characters which should not be part of those columns.

By concatenation, you can have as many columns as possible.

CAUTION - Result of entire concatenation should not be having length more than 255. Hence, F2&"@@@"&G2 should not have more than 255 characters.

Another alternative is to use below Array formula -

=INDEX(C2:C12,MATCH(1,--NOT(ISLOGICAL(IF(A2:A12=F2,IF(B2:B12=G2,C2:C12)))),0))

Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.