Sat 13 Feb 2016

Challenge 36 - Generate Triangular Numbers

By |Saturday, February 13th, 2016|Categories: Challenges|Tags: , , , , , , |1 Comment

If interested in details about Triangular Numbers, you can refer to following link (though it is not needed and one look at the sequence, you will understand what it is)

https://en.wikipedia.org/wiki/Triangular_number

It is, basically, the following sequence -

1, 3, 6, 10, 15, 21, 28, 36, 45, 55, 66, 78, 91, 105, 120, 136, 153, 171, 190, 210, 231, 253, 276, 300, 325, 351, 378, 406

The challenge is to write a formula which can be put down in any cell and which when dragged down will generate the above sequence.

Hence, if I put the formula in say A1, it should generate the above sequence. Same formula if put in C4 and dragged down, should generate the above sequence. Same formula if put in F8 and dragged down, should generate the above sequence. Hence, the formula should not require any tweaking to generate the sequence in different columns. Same formula should work in any cell.

The solution to the above challenge will be published after a month i.e. on 13-Mar-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 05 Sep 2015

Tips & Tricks 107 - Autofill on a Filtered List

By |Saturday, September 05th, 2015|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

Everybody is quite aware about Autofill. There are various ways to Autofill. Refer to following article for Autofill -

Article 7 – Generate a Sequence of Numbers

Now, apply a filter on your range and all the techniques fail. If you drag, all cells are filled with 1 and no other techniques also work. The reason is that Autofill works only on a contiguous range. Once, you apply filter, the range becomes non-contiguous.

Now, here comes the trick -

1. Apply the filter.
2. Let's assume that first row is 3 and you wanted to fill it in column B.
3. Put following formula in B3 and drag down
=COUNTIF($B$1:B2,"<>"&"")

If you don't want to drag down -
3.1 Put the above formula in B3.
3.2 Select all the cells including B3.
3.2 Press F2
3.3 CTRL+Enter

Above steps will fill the filtered list with 1, 2, 3.....

Sat 08 Aug 2015

Tips & Tricks 103 - I need to fill in A-Z or a-z very often. What to do

By |Saturday, August 08th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

1. Put =CHAR(96+ROW()) in cell A1.
2. Drag down till A26 to generate a to z.
3. Put =CHAR(64+ROW()) in cell B1.
4. Drag down till B26 to generate A to Z.
5. File > Options > Advanced > Go down till you find Edit Custom Lists (You have to go down till bottom) and click this

1

6. Now, you can import your range as below.

1

7. Now, any time you need this sequence just put a or A and drag down. The sequence will be generated.

Fri 17 Jul 2015

Solution - Challenge 17 – Sum if Y Appears More than One Time Consecutively

By |Friday, July 17th, 2015|Categories: Solutions|Tags: , , , , , , , |0 Comments

Below is a proposed solution for challenge Solution - Challenge 17 – Sum if Y Appears More than One Time Consecutively Maximum

1. Introduce a helper column in column D and put following formula in D2 and drag down -

=IF(B2="N",0,IF(AND(A2=$F$2,A1=A2),1+D1,1))

2. Put following formula in F3

=IF(ISNUMBER(MATCH(F2,A:A,0)),SUM(OFFSET(C1,MATCH(MAX(D2:D22),D2:D22,0),
0,-MAX(D2:D22),1)),"")

Mon 13 Jul 2015

Solution - Challenge 13 – Generating Digit Product Sequences

By |Monday, July 13th, 2015|Categories: Solutions|Tags: , , , |0 Comments

A proposed solution is listed below for the challenge - Challenge 13 – Generating Digit Product Sequences

Put a starting seed in A1.

Put following formula in A2 and drag down -

=A1+PRODUCT(INDEX(--MID(SUBSTITUTE(A1,0,1),ROW(INDIRECT("1:"&LEN(A1))),1),,))

For a seed value of 1 in A1, it will generate following sequence -

1, 2, 4, 8, 16, 22, 26, 38, 62, 74, 102, 104, 108, 116, 122, 126, 138, 162, 174, 202, 206, 218, 234, 258, 338, 410, 414, 430, 442, 474, 586, 826, 922, 958, 1318, 1342, 1366, ...

 

Mon 13 Jul 2015

Solution - Challenge 11 – Generate a Repeating Number Sequence – II

By |Monday, July 13th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

Below is a proposed solution for challenge - Challenge 11 – Generate a Repeating Number Sequence – II

Put following formula and drag down -

=ROUNDUP(ROWS($1:1)/3,0)

This will repeat the sequence 111222333444555...........

Mon 13 Jul 2015

Solution - Challenge 10 – Generate a Repeating Number Sequence – I

By |Monday, July 13th, 2015|Categories: Solutions|Tags: , , , , |1 Comment

Below is the proposed solution for challenge - Challenge 10 – Generate a Repeating Number Sequence – I

Put following in a cell and drag down -

=MOD(ROWS($1:1)-1,5)+1

This will generate a sequence 1, 2, 3, 4, 5 and will repeat this.

Sat 11 Jul 2015

Solution - Challenge 1 – Single Formula for Fibonacci Numbers

By |Saturday, July 11th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

This is a possible solution to Challenge 1 – Single Formula for Fibonacci Numbers

There can be many solutions to a given problem in Excel. Below are solutions proposed by me -

A1 and drag down -

=IF(ROW()<3,ROW()-1,SUM(OFFSET($A$1,ROW()-3,0,2)))

=IF(ROW()<3,ROW()-1,SUM(INDIRECT("A"&ROW()-2&":A"&ROW()-1)))

If you belong to that class where you worry about volatality of OFFSET and INDIRECT, use below which uses INDEX and INDEX is a non-volatile function -

=IF(ROW()<3,ROW()-1,SUM(INDEX(A:A,ROW()-2):INDEX(A:A,ROW()-1)))