## Tips & Tricks 163 - Convert Alphabets to Numbers

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

Sat 30
Sep 2017

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

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

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

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**

Sun 27
Mar 2016

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

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**

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

Sat 06
Feb 2016

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

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

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

Sun 12
Jul 2015

Below is a proposed solution for the challenge - Challenge 2 – Sum of Numbers in the Diagonal of a Square

Put following formula for the answer -

=IF(L2="B",SUMPRODUCT((OFFSET(H8,0,0,-MIN(8,L1),-MIN(8,L1)))*(ROW(OFFSET(H8,0,0,-MIN(8,L1),-MIN(8,L1)))=COLUMN(OFFSET(H8,0,0,-MIN(8,L1),-MIN(8,L1))))),SUMPRODUCT((OFFSET(A1,0,0,MIN(8,L1),MIN(8,L1)))*(ROW(OFFSET(A1,0,0,MIN(8,L1),MIN(8,L1)))=COLUMN(OFFSET(A1,0,0,MIN(8,L1),MIN(8,L1))))))

The worksheet for the solution can be downloaded from Solution - Challenge 2 – Sum of Numbers in the Diagonal of a Square