Sat 16 Dec 2017

Challenge 67 - Generate a Particular Sequence

By |Saturday, December 16th, 2017|Categories: Challenges|Tags: , , , , , |0 Comments

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.

Sat 30 Sep 2017

Tips & Tricks 163 - Convert Alphabets to Numbers

By |Saturday, September 30th, 2017|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

If you want to convert a, b, c....z to 1, 2,3....26, the you can use following type of formula -

=CODE(UPPER(A1))-64

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

Sat 16 Jul 2016

Challenge 47 - Generate Pentagonal Series

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

Pentagonal number series is following - 1, 5, 12, 22, 35, 51, 70, 92, 117, 145, 176....

You need to write an Excel formula which can be dragged down and generates the above sequence.

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

Sat 21 May 2016

Article 39 - Sorting in Excel

By |Saturday, May 21st, 2016|Categories: Articles|Tags: , , , , , , , , , , |0 Comments

First thing first – Excel doesn’t sort as per ASCII character codes which we generally expect. It has its own sorting logic which is detailed below for Ascending sort (reverse the same for Descending sort).

The order followed in an Ascending sort is Number > Text > Alphanumeric text > Logical values > Error values > Blanks

(more…)

Sun 27 Mar 2016

Solution - Challenge 37 – Find nth Alphabet from the Bottom

By |Sunday, March 27th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 37 – Find nth Alphabet from the Bottom.

Put following formula as Array formula in E1

=IF(SUM(--(ABS(77.5-IFERROR(CODE(UPPER(A1:A100)),0))<13))>=C1,
INDEX(A1:A100,LARGE((ABS(77.5-IFERROR(CODE(UPPER(A1:A100)),0))<13)
*ROW(A1:A100),C1)),"")

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.

The workbook containing the solution can be downloaded from Solution - Challenge 37 - nth Alphabet from the Bottom.

Sat 27 Feb 2016

Challenge 37 - Find nth Alphabet from the Bottom

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

Suppose, you have been given a layout as below. The challenge before you is to write a formula to find nth alphabet from bottom. If no criterion is met, blanks should be returned.

C1 hold the value of that nth.

For C1 = 5, answer would be k.
for C1 = 10, answer would be blanks.

The Excel sheet related to this can be downloaded from Challenge 37 - nth Alphabet from the Bottom

1

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

Sat 06 Feb 2016

Tips & Tricks 129 - Generate Non Repeating Random Numbers through Formula

By |Saturday, February 06th, 2016|Categories: Tips and Tricks|Tags: , , , |0 Comments

Suppose, you want to generate non-repeating random numbers between 1 to 30, you can use following formula in A2 and drag down

=IFERROR(AGGREGATE(14,6,ROW($1:$30)*NOT(COUNTIF($A$1:$A1, ROW($1:$30))),
RANDBETWEEN(1,30-ROWS($1:1)+1)),"")

Note: $A$1:$A1 is with reference to A2 as you put formula in A2 and dragged down. Suppose, you had put the formula in G4, this should be replaced with $G$3:$G3.

If your starting and ending numbers are in B1 and C1, use below formula

=IFERROR(AGGREGATE(14,6,ROW(INDIRECT($B$1&":"&$C$1))*
NOT(COUNTIF($A$1:$A1,ROW(INDIRECT($B$1&":"&$C$1)))),
RANDBETWEEN($B$1,$C$1-ROWS($1:1)+1)),"")

For versions, prior to 2010 following basic construct can be used (Build error handling depending upon the version. For example, Excel 2007 will support IFERROR whereas 2003 supports ISERROR) -

=LARGE(INDEX(ROW($1:$30)*NOT(COUNTIF($A$1:$A1, ROW($1:$30))),,),
RANDBETWEEN(1,30-ROW(A1)+1))

Mon 09 Nov 2015

Solution - Challenge 27 – Count the Number of Alphabets and Numerals and Other Characters

By |Monday, November 09th, 2015|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 27 – Count the Number of Alphabets and Numerals and Other Characters.

The formula for counting number of alphabets

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(65:90)),"")))

The formula for counting number of numerals

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,"")))

The formula for counting other characters

=LEN(SUBSTITUTE(A1," ",""))-C1-C2

Where C and C2 are number of alphabets and numerals.

Tue 14 Jul 2015

Solution - Challenge 15 – Floyd’s Triangle

By |Tuesday, July 14th, 2015|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a proposed solution for the challenge Challenge 15 – Floyd’s Triangle

Put this formula anywhere in your sheet and drag right and down

=IF(COLUMNS($A:A)>ROWS($1:1),"",IF(ROWS($1:1)=1,1,ROWS($1:1)-2+
COLUMNS($A:A)+INDIRECT(ADDRESS(ROW()-1,COLUMN()-COLUMNS($A:A)+1,1))))

The solution workbook is located at Solution - Challenge 15 – Floyd’s Triangle