Tue 13 Jun 2017

Solution 62 - Produce the Sum for Merged Cells Headers

By |Tuesday, June 13th, 2017|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution for the challenge - Challenge 62 - Produce the Sum for Merged Cells Headers

Put following formula B14 and drag right and down

=SUM(OFFSET($A$1,ROWS($1:1),MATCH(B$13,$1:$1,0)-1,,IFERROR(MATCH(C$13,$1:$1,0),COUNTA($2:$2)+1)-MATCH(B$13,$1:$1,0)))

Sat 13 May 2017

Challenge 62 - Produce the Sum for Merged Cells Headers

By |Saturday, May 13th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Download the workbook related to this challenge - Sum for Merged Cells

This time challenge is to produce the sum where header is merged cells. For the layout like below -

1

You need to write a single formula which can be dragged right and down to generate the sum for below table

1

The solution to this challenge will be published after a month i.e. on 13-Jun-17.

Mon 01 May 2017

Solution - Challenge 61 - Generate Multiplication Table

By |Monday, May 01st, 2017|Categories: Solutions|Tags: , , , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 61 - Generate Multiplication Table

Put following formula and drag right and down -

=ROWS($1:1)*COLUMNS($A:A)

Sat 01 Apr 2017

Challenge 61 - Generate Multiplication Table

By |Saturday, April 01st, 2017|Categories: Challenges|Tags: , , , , , , , , , |4 Comments

This time, I want to set a challenge which is not difficult and useful for your kids.

The challenge is to write a formula which can be dragged right and down to generate a multiplication table.

1

The solution to this challenge will be published after a month i.e. on 1-May-17.

Sat 07 Jan 2017

Tips & Tricks 153 - Sum only Visible Columns

By |Saturday, January 07th, 2017|Categories: Tips and Tricks, VBA|Tags: , , , , , , , , |2 Comments

You can use SUBTOTAL or AGGREGATE function to sum visible rows but Excel doesn't provide the facility to sum only visible columns. Look below. Columns B and D are hidden. Hence, our function should sum up only A, C, E and F. G2 has the formula =SUM(A2:F2)

If you hide the columns, the sum stays the same whether columns are hidden or visible.

1

Unfortunately, Excel doesn't provide any native functionality to accomplish this. This will have to be done through a VBA function. We will write a VBA function "SumVisCols" which can be called like =SumVisCols(A2:F2)

(You can give any range not only A2:F2)

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given and change the bold lines as per your requirement
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

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

Mon 23 May 2016

Solution - Challenge 41 – Sum the Maximum Number where duplicates Exist

By |Monday, May 23rd, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 41 – Sum the Maximum Number where duplicates Exist

Enter the below formula as Array Formula i.e. not 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.

=SUM((MATCH(A2:A10,IF(COUNTIFS(A2:A10,A2:A10,B2:B10,">"&B2:B10)=0,
A2:A10),0)=ROW(A2:A10)-MIN(ROW(A2:A10))+1)*B2:B10)

The workbook illustrating the above solution can be downloaded from Solution - Challenge 41 - Sum the Maximum Number where duplicates Exist

Sat 23 Apr 2016

Challenge 41 - Sum the Maximum Number where duplicates Exist

By |Saturday, April 23rd, 2016|Categories: Challenges|Tags: , , , , , |1 Comment

Suppose, you have been given following and you will need to find duplicates in column A and sum the maximum values from column B. If duplicates don't exist, values will be taken as they are. The values which needs to be summed up are colored. The answer would be 123 in this.

1

The workbook related to this challenge can be downloaded from Challenge 41 - Sum the Maximum Number where duplicates Exist

The answer to this challenge would be published after a month i.e. on 23-May-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.