Sat 27 Aug 2016

Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

By |Saturday, August 27th, 2016|Categories: Challenges|Tags: , , , , , , , , , , , , , , |0 Comments

Tabula Recta is supposed to be one of the foundation stones of cryptography. This was one of the earliest forms of encryption. The more about this can be read at https://en.wikipedia.org/wiki/Tabula_recta

One look at below and you will understand what it is. In upcoming challenges in future, Tabula Recta will be used. The first row is from A to Z and first column is from A to Z (Yellow row and column). You need to populate all other values by a single formula. (Yellow row and column is something which you can put as constant. But white area is something which you need to populate)

1

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

Sun 21 Aug 2016

Tips & Tricks 158 - Overcoming column_index_number problem in VLOOKUP when a column is inserted / deleted

By |Sunday, August 21st, 2016|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

One of the negative points which gets attributed to VLOOKUP is that whenever a column is added / inserted within the range of VLOOKUP, the column index number doesn't change. Hence, it gives wrong result. Let's consider below dataset and for a given Emp ID, I need gender of that person. Hence, for Emp ID, 754761, the formula would be =VLOOKUP(754761,$A:$G,6,0) and answer would be F. Now, let's delete a column and now the answer would be lelia.vang@gmail.com as column_index_number is still 6. Now, add a column and the answer would be Vang as column_index_number is still 6.

(more…)

Sat 20 Aug 2016

Excel Quiz 45

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

Excel Quiz 45

A general quiz on Excel.

Sat 20 Aug 2016

Tips & Tricks 157 - Search for Online Templates Greyed Out / Disabled

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

When we create a new workbook through File > New, we get a box where we can search for online templates. But, at times, it may be greyed out or disabled i.e. you can not type out anything out there.

1

To correct this, you need to enable internet setting for Excel.

File > Options (You can also invoke Excel options through shortcut ALT+T+O) > Trust Center > Trust Center Settings > Privacy Options > Allow Office to connect to Internet and Check this box.

1

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

Tue 16 Aug 2016

Downloads 13 - Template 11 - Invoice Template

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

The invoice template is specifically designed for Service Invoices in India but this can be used anywhere in the world with little customization.

There are two templates, one which populates the amount in words in Rupees and Paise and one which populates in Dollar and Cents. If you need to change these currencies, you can write to me to change them.

(more…)

Sat 13 Aug 2016

Article 42 - Generating an Odd Order Magic Square in Excel (VBA)

By |Saturday, August 13th, 2016|Categories: Articles|Tags: , , , , , , , , , , , |0 Comments

A magic square needs no introduction and we come across it many times. A magic square is a square grid and the minimum size of a magic square is 3x3. The whole numbers in magic square appear only once and all cells are filled. The horizontal rows, vertical columns and main and secondary diagonals all add up to the same number. This number is called magic constant. The more about magic square can be read here - https://en.wikipedia.org/wiki/Magic_square

(more…)

Sat 13 Aug 2016

Challenge 49 - Whether a Word is Isogram or Not

By |Saturday, August 13th, 2016|Categories: Challenges|Tags: , , , , , , , , , |0 Comments

This time, continuing my effort to make challenge on English language, the challenge is on Isogram. An Isogram is that word in English which doesn't repeat any alphabet. For example: "Botany" is an Isogram but "Britain" is not as "i" repeats.

Hence, this time, you need to make a formula which returns "Isogram" if the word is Isogram othewise returns "Not Isogram"

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

Sun 07 Aug 2016

Excel Quiz 44

By |Sunday, August 07th, 2016|Categories: Quizzes|Tags: , , , , , , , |0 Comments

Excel Quiz 44

A general quiz on Excel.

Sat 06 Aug 2016

Tips & Tricks 156 - Get Workbook's Directory from Formula

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

If your workbook is located in say C:\Excel\MyDocs, the formula to retrieve the directory for this would be

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)

Note - For this formula to work, you workbook must be saved at least once.