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: , , , , , , , , |4 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.

Sat 18 Jun 2016

Article 40 - Order of Operations in Formula

By |Saturday, June 18th, 2016|Categories: Articles|Tags: , , , , , , , , , , , , , , |0 Comments

Follow the BODMAS rule

B – Brackets (Parentheses)
O – Orders (Powers/Exponents)
D – Division
M – Multiplication
A – Addition
S – Subtraction (more…)

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.

Tue 05 Jan 2016

Solution - Challenge 31 – Increment All Digits by 1

By |Tuesday, January 05th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the problem Challenge 31 – Increment All Digits by 1

Use below formula -

=A1+REPT(1,LEN(A1))

Sat 05 Dec 2015

Challenge 31 - Increment All Digits by 1

By |Saturday, December 05th, 2015|Categories: Challenges|Tags: , , , , , , |4 Comments

This time's challenge is a short one but it may take some time to think through.

Suppose, you have a number in cell A1. The challenge is to add 1 in all digits and come with a result.

Hence,

If A1=28, result would be 39.

If A1 = 123, result would be 123+111 = 234

If A1= 912, result would be 912+111 = 1023

If A1=1999, result would be 1999+1111 = 3110

If A1=9999, result would be 9999+1111 = 11110

You can choose to post your answer in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 05-Jan-16.

Sat 11 Apr 2015

Tips & Tricks 86 - Quickly Access AutoSum

By |Saturday, April 11th, 2015|Categories: Tips and Tricks|Tags: , , , , |1 Comment

11

Autosum is a much widely used feature in Excel.  It is available in two places in Excel 2013. One in Home tab and one in Formulas tab.

You can quickly access the AutoSum by the shortcut key ALT+

(more…)