Tue 30 Aug 2016

Solution - Challenge 48 - Whether a Sentence is Pangram or Not

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

Below is a possible solution to the challenge - Challenge 48 - Whether a Sentence is Pangram or Not

=IF(ISNUMBER(SUMPRODUCT(MATCH(ROW($65:$90),INDEX(CODE(MID(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(UPPER(A1)," ",""),".",""),",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1," ",""),".",""),",","")))),1)),,),0))),"Pangram","Not Pangram")

 

Sat 27 Aug 2016

Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

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

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.

Sat 20 Aug 2016

Downloads 06 - Template 06 - Risk Log based on PMBOK 5

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

< The template can be downloaded from Template 06 - Risk Log >

This time, this is a unique kind of Risk Log. This covers all 5 phases of Risk Management and is based on PMI PMBOK 5.

The template is highly customizable and Risk Score and PI Matrix coloring will be done automatically.

1

1

1

1

1

1

1

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

Sat 13 Aug 2016

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

By |Saturday, August 13th, 2016|Categories: Articles, VBA|Tags: , , , , , , , , , , , |1 Comment

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: , , , , , , , , , |1 Comment

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 and Tricks 142 - Determine Number of Working Days in a Year

By |Saturday, August 06th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , |1 Comment

Suppose, you have been given a year in A1 (Say A1 = 2016) and you need to determine the number of working days in a Year, then your formula to determine number of working days would be -

=NETWORKDAYS("1JAN"&A1,"31DEC"&A1)

The above formula is based on the fact that Saturdays and Sundays are weekends. Starting Excel 2010, you can control the weekends in the formula and function is NETWORKDAYS.INTL

=NETWORKDAYS.INTL("1JAN"&A1,"31DEC"&A1,"0000110")

In the string "000110" - First digit is Monday and last digit is Sunday. 1 defines that particular day as weekend.

If you have got your list of holidays in a range say B1:B20 (B1:B20 should contain dates in date format), you can have following formulas

=NETWORKDAYS("1JAN"&A1,"31DEC"&A1,B1:B20)

=NETWORKDAYS.INTL("1JAN"&A1,"31DEC"&A1,"0000110",B1:B20)

Tue 02 Aug 2016

Solution - Challenge 46 – Compute Numerological Sum for a Name

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

Below is a possible solution to the challenge – Challenge 46 – Compute Numerological Sum for a Name

The formula to calculate Numerological Sum for a Name would be -

=MOD(SUMPRODUCT(MOD(CODE(MID(SUBSTITUTE(LOWER(A1)," ",""),
ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1))+2,9)+1)-1,9)+1