Tue 27 Sep 2016

Solution - Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

By |Tuesday, September 27th, 2016|Categories: Solutions|Tags: , , , , , , , , , , , , , , |0 Comments

Below is a possible solution to the Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

Put following formula and drag down and right

=CHAR(MOD(ROWS($1:1)+COLUMNS($A:A)-2,26)+65)

A workbook containing the above solution can be downloaded from Solution - Challenge 50 - Cryptography Challenge 3 - Generate Tabula Recta

Sat 24 Sep 2016

Challenge 52 - Generate the Sequence 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5...

By |Saturday, September 24th, 2016|Categories: Challenges|Tags: , , , , |1 Comment

This time challenge before you is to write a formula which generates a sequence where every digit appears that many times as that digit. Hence, 3 will repeat 3 times, 8 will repeat 8 times and so on..Hence, the exact sequence would be 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5,6,6,6,6,6,6,7,7,7,7,7,7,7.....

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

Sat 17 Sep 2016

Downloads 07 - Template 07 - A Flexible Lessons Learned Template

By |Saturday, September 17th, 2016|Categories: Downloads|Tags: , , , , , , , , |0 Comments

< The template can be downloaded from Template 07 - Lessons Learned Template >

This time, the template presented is for Lessons Learned. The template covers both what went well and what didn't go well. The category of learning can be customized. I have already put 3 categories which are based on PMI Process Area, PMI Knowledge Areas, PMI Constraints and one category for your organization determined categories.

You can use Setup tab to choose which category to use.

For project name, project Manager Name and other project related info, you can insert a Cover Page tab.

1

1

Sat 17 Sep 2016

Tips & Tricks 145 - Determine the First Sunday or any other Day given Weeknumber

By |Saturday, September 17th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

If you have been given a week number and has been asked to find the first Sunday for that week, you can use following formula

=CEILING(("1JAN"&A1)-14,7)+8+7*(5-1)

Where A1 has the year say A1=2016

5 is the Week Number which you can replace.

For Finding Monday, add 1 in the formula, add 2 for Tuesday and so on.

The above formula assumes that WEEKNUM function has Sunday as the starting day for the week. If you have any other day for the week as the starting day of the week, you will have to customize the above formula as per the need.

 

Tue 13 Sep 2016

Solution - Challenge 49 – Whether a Word is Isogram or Not

By |Tuesday, September 13th, 2016|Categories: Solutions|Tags: , , , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 49 – Whether a Word is Isogram or Not

Use following formula -

=IF(SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW($65:$90)),""))>1))
=1,"Not Isogram","Isogram")

Sat 10 Sep 2016

Challenge 51 - Another Word Challenge - Whether Given Words are Anagram or not

By |Saturday, September 10th, 2016|Categories: Challenges|Tags: , , , , , , , , , |1 Comment

Given a word, an anagram is made by using all letters of the given word exactly once. For example, For example, my name "vijay" can be written as "jaivy", "yajvi" and so on....Some well known anagrams are

Lead - Flea
Admirer - Married
Listen - Silent
Mother in Law - Woman Hitler
Eleven Plus Two - Twelve Plus One

So, below is the challenge. Put original word in A1 and anagram in B1. You need to put a formula which gives the result "Anagram" if B1 has anagram of A1 and gives "Not Anagram" if B1 is not anagram of A1.

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

Sat 03 Sep 2016

Tips & Tricks 144 - Enter the Last Save Date and Time

By |Saturday, September 03rd, 2016|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

1. Open your workbook and ALT+F11
2. Locate your Workbook name in Project Explorer Window
3. Right click on your workbook name > Insert > Module
4. Copy paste the Macro code given
5. Save your file as .xlsm

To get Last Save Date, enter following in a cell
=LastSaveDate()

To get Last Save Time, enter following in a cell
=LastSaveTime()

To get Last Save Date & Time Both, enter following in a cell
=LastSaveDateTime()

Note - You will have to format your result cells appropriately in Date / Time / Timestamp format

< Download the workbook illustrating the same Last Saved Date Time >

 

 

Sat 03 Sep 2016

Excel Quiz 45

By |Saturday, September 03rd, 2016|Categories: Quizzes|Tags: , , , |0 Comments

Excel Quiz 45

A general quiz on Excel.