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 20 Feb 2016

Excel Quiz 32

By |Saturday, February 20th, 2016|Categories: Quizzes|Tags: , , , , |0 Comments

A Multiple Choice Quiz

A general quiz on Excel

Sat 20 Feb 2016

Tips & Tricks 130 - Generate Sequential Numbers and Repeat them

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

Suppose, you have been given the task to generate a sequence of numbers and repeat them. For example -

1,2,3,4,1,2,3,4,1,2,3,4

You can use the below formula and drag down -

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

Replace 4 with with any other number to generate any other sequence. Hence, if you want to generate 1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10 then formula becomes -

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

The structure of the formula is

=MOD(ROWS($1:1)-1,X)+Y

X - Number of numbers
Y - Starting Number

Utilizing above formula, you want to generate the sequence 5,6,7,8,9,10,5,6,7,8,9,10,5,6,7,8,9,10, then use below formula (You need 6 numbers and stating number is 5)

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

Thu 18 Feb 2016

Downloads 01 - Template 01 - A Different Kind of Pareto Chart Template

By |Thursday, February 18th, 2016|Categories: Downloads|Tags: , , , , , , , , , , , , , |0 Comments

Download this Pareto Chart Template from Template 01 - Pareto Chart Template

This Pareto Chart Template is different from commonly found Pareto Chart Templates. It not only gives a slider bar to adjust your percentage which is typically 80% to any other percentage but it also gives a vertical bar. The combination of vertical bars and horizontal helps you to find out problem areas in a ziffy.

1

1

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.

Sun 14 Feb 2016

Article 36 - VBA - User Names in Excel

By |Sunday, February 14th, 2016|Categories: Articles, VBA|Tags: , , , , , , , , |0 Comments

Many times, we are required to fetch User Names in Excel. Unfortunately, getting User Names in Excel is possible only through VBA. These are very small pieces of VBA codes and even a person not knowing VBA can make use of them by following the instructions here.

I am going to discuss the various type of User Names which we may be required to extract.

(more…)

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.

 

Sat 06 Feb 2016

Excel Quiz 31

By |Saturday, February 06th, 2016|Categories: Quizzes|Tags: , , , , |0 Comments

Excel Quiz 31

A general quiz on Excel

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

Tue 02 Feb 2016

Solution - Challenge 33 – Convert Matrix into Linear Column – II

By |Tuesday, February 02nd, 2016|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 33 – Convert Matrix into Linear Column – II.

Enter below formula and drag down -

=IF(OFFSET($A$1,MOD(ROWS($1:1)-1,COUNTA($A:$A)),ROUNDUP(ROWS($1:1)
/COUNTA($A:$A),0)-1)=0,"",OFFSET($A$1,MOD(ROWS($1:1)-1,COUNTA($A:$A)),
ROUNDUP(ROWS($1:1)/COUNTA($A:$A),0)-1))

The workbook containing the above solution can be downloaded from Solution - Challenge 33 – Convert Matrix into Linear Column – II.