Tue 16 Jan 2018

Solution - Challenge 68 - Generate a Particular Sequence

By |Tuesday, January 16th, 2018|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the problem Challenge 67 - Generate a Particular Sequence -

This consists of 2 logic.

  1. Generate Triangular number. The formula for which is n*(n+1)/2
  2. Generate Column Labels (A, B, AA, ZZ....) from numbers, the formula for which is

=SUBSTITUTE(ADDRESS(1,3,4),1,"") where 3 is for column C. So, we need to vary this 3.

The combined formula would be -

=SUBSTITUTE(ADDRESS(1,(ROW(1:1)-1)*ROW(1:1)/2+1,4),1,"")

Sat 16 Dec 2017

Challenge 68 - Generate a Particular Sequence

By |Saturday, December 16th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Study the sequence below and you need to write a formula which when dragged down should generate the below sequence.

For visual representation purpose, the sequence is in 5 columns but you need to generate the sequence in one column only.

You need to generate upto 100 entries.

The solution to this challenge will be published after a month i.e. on 16-Jan-18.

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

Tue 22 Nov 2016

Solution - Challenge 54 - Make a Sequence like A_B__C___D____E_____F......

By |Tuesday, November 22nd, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 54 - Make a Sequence like A B C D E F......

Put below formula as array formula in a cell and drag down

=IFERROR(CHAR(MATCH(ROWS($1:1),(ROW($1:$26)*(ROW($1:$26)+1))/2,0)+64),"")

(more…)

Mon 24 Oct 2016

Solution - Challenge 52 - Generate the Sequence 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5...

By |Monday, October 24th, 2016|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the challenge Challenge 52 - Generate the Sequence 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5...

Put following formula and drag down

=FLOOR((1+((1+8*ROWS($1:1)-1)^0.5))/2,1)

Sat 22 Oct 2016

Challenge 54 - Make a Sequence like A_B__C___D____E_____F......

By |Saturday, October 22nd, 2016|Categories: Challenges|Tags: , , , , , , |1 Comment

This time challenge before you is to write a formula which can be dragged down and leaves so many blank cells - 1 before that alphabet's position in English Language. Hence, the sequence will start with A, will leave 1 blank cell, then B, then 2 blank cells, then C, then 3 blank cells, then D, then 4 blank cells, then E, then 5 blank cells, then F....................................................................then 24 blank cells, then Y and finally 25 blank cells and then Z.

(more…)

Sat 24 Sep 2016

Challenge 52 - Generate the Sequence 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5...

By |Saturday, September 24th, 2016|Categories: Challenges|Tags: , , , , |1 Comment

This time challenge before you is to write a formula which generates a sequence where every digit appears that many times as that digit. Hence, 3 will repeat 3 times, 8 will repeat 8 times and so on..Hence, the exact sequence would be 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5,6,6,6,6,6,6,7,7,7,7,7,7,7.....

The solution to above challenge will be published after a month i.e. on 24-Oct-16.

Sat 20 Aug 2016

Tips and Tricks 143 - Increment a Number when Workbook is Opened (Invoice or PO Number)

By |Saturday, August 20th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double Click on "This Workbook"
5. Copy paste the Macro code given - Replace Sheet1 and B1 as per your need
6. Save your file as .xlsm if you intend to reuse Macro again.

< A workbook illustrating this can be downloaded from Invoice Number Generator >

'**** Macro Starts

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("B1") = Worksheets("Sheet1").Range("B1") + 1
End Sub

Tue 16 Aug 2016

Solution - Challenge 47 - Generate Pentagonal Series

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

Below isĀ  a possible solution to the problem - Challenge 47 - Generate Pentagonal Series

Put following formula and drag down -

=ROWS($1:1)*(3*ROWS($1:1)-1)/2