Sat 30 Apr 2016

Excel Quiz 37

By |Saturday, April 30th, 2016|Categories: Quizzes|Tags: , , , , , |0 Comments

Excel Quiz No. 37

A general quiz on Excel

Sat 30 Apr 2016

Tips & Tricks 135 - I input (c) or (C) and it gets converted to Copyright Symbol..I don't want this

By |Saturday, April 30th, 2016|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

There are many ways to overcome this.

1. a. Put following in a cell and press Enter
c)
b. Go back to the cell and type first bracket to complete (c)

2. a. You can type (c) preceded with some special character and press enter.
b. Go back to that cell and delete that special character.
c. I have tried this with following special characters and found it working
!(c)
#(c)
!(c)
$(c)
%(c)
_(c)
\(c)
?(c)

2. a. Put only following entry in the cell
(c
b. CTRL+F
c. In Find What: box, put
(c
d. In Replace With: box, put
(c)

Note - The above will work with c) and replace with (c) also.

3. a. Write (c) in a cell and press space bar
b. Now (c) will get converted to copyright symbol. CTRL+Z
c. Now, (c) will be (c) only but it will have space after that.
d. If we don't want this additional character, we can replace this with space with blanks in CTRL+F window. In Find What: box, press space bar once (nothing in Replace With: box) and Replace.

4. For permanent correction - You can go to File > Options > Proofing > AutoCorrect Options > Locate (c) > Either delete this entry or put (c) in With:

1

Note  - You can uncheck "Replace text as you type" to disable Replacing completely. But this would be applicable for all entries, hence not recommended.

Tue 26 Apr 2016

Solution - Challenge 39 – Cryptography Challenge 1 – Caesar’s Shift Cipher

By |Tuesday, April 26th, 2016|Categories: Solutions|Tags: , , , , , , , , , , , |1 Comment

Below is a possible solution to the challenge Challenge 39 – Cryptography Challenge 1 – Caesar’s Shift Cipher.

Put below formula in G2 and drag down -

=IFERROR(CHAR(IF(CODE(LOWER(D2))+$B$3>122,CODE(D2)+$B$3-26,
CODE(D2)+$B$3)),"")

The workbook containing the solution can be downloaded from Solution - Challenge 39 - Caesar's Shift Cipher.

Sat 23 Apr 2016

Challenge 41 - Sum the Maximum Number where duplicates Exist

By |Saturday, April 23rd, 2016|Categories: Challenges|Tags: , , , , , |1 Comment

Suppose, you have been given following and you will need to find duplicates in column A and sum the maximum values from column B. If duplicates don't exist, values will be taken as they are. The values which needs to be summed up are colored. The answer would be 123 in this.

1

The workbook related to this challenge can be downloaded from Challenge 41 - Sum the Maximum Number where duplicates Exist

The answer to this challenge would be published after a month i.e. on 23-May-16.

Sat 23 Apr 2016

Downloads 02 - Template 02 - A Highly Customizable Perpetual Yearly Calendar Template in Excel

By |Saturday, April 23rd, 2016|Categories: Downloads|Tags: , , , , , , , , , , , , , |0 Comments

Download link for this Calendar Template 02 - Calendar Template Yearly

The second template in the series is a calendar. Just download it once and use every year. Following are the features -

1. The calendar is perpetual in nature. It supports all the years from 1900 to 9999. You just need to change the year every year or you can leave the year field blank. In that case, it would take the current year as the year.
2. It features a list where you can put important dates and those dates will automatically be shaded.
3. You can define your weekends. It can be one weekend or two weekends.
4. You can define your start day of the week. It can be any day between Mon to Sun.
(more…)

Sat 16 Apr 2016

Tips & Tricks 134 - Last Working Day of the Year

By |Saturday, April 16th, 2016|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

If a year is given in A1 say 2016, below formula can be used to know the last working day of the year (format the result as date)

=WORKDAY("1JAN"&A1+1,-1)

The above formula assumes that your weekends are Saturday and Sunday.

But, if your weekends are different (e.g. in gulf countries), you can use following formula -

=WORKDAY.INTL("1JAN"&A1+1,-1,"0000110")

Where 0000110 is a 7 character string, 1 represents a weekend and 0 is a working day. First digit is Monday and last digit is Sunday. The above example is for Gulf countries where Friday and Saturday are weekends.

You also have option to give a range which has holidays. In that case, your formula would become

=WORKDAY("1JAN"&A1+1,-1,D1:D10)

=WORKDAY.INTL("1JAN"&A1+1,-1,"0000110",D1:D10)

Where range D1:D10 contains the list of holidays.

Sat 16 Apr 2016

Article 38 - 10 Features I would like to see in Excel

By |Saturday, April 16th, 2016|Categories: Articles|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , |0 Comments

Like all of you, I am dependent upon day to day work in Excel. Excel is a software which is like ocean, unlimited in bounds. It has almost all features but it doesn't have few which everybody cherishes. All of us want something or the other. Below is a list of features which I would like Excel to have.

1. Making Volatile Functions Non-volatile on the basis of a Parameter

We all like TODAY(), NOW(), RAND() and RANDBETWEEN() (there are other volatile functions also). They are very useful but suffer a fatal flaw, if something gets recalculated, they always get recalculated also. Hence, if I am using TODAY() to derive today's day and if I open the sheet tomorrow, TODAY() will change tomorrow's date. I wish that all volatile functions should carry a parameter so that they should not recalculated when the sheet is recalculated. For example, we should have something like TODAY(N), NOW(N),RAND(N) and RANDBETWEEN(N) where N
(more…)

Sat 16 Apr 2016

Excel Quiz 36

By |Saturday, April 16th, 2016|Categories: Quizzes|Tags: , , , , |0 Comments

Quiz no. 36

A general quiz on Excel

Tue 12 Apr 2016

Solution - Challenge 38 – Formula for Top 5

By |Tuesday, April 12th, 2016|Categories: Solutions|Tags: , , |0 Comments

Below is a possible solution to the challenge Challenge 38 – Formula for Top 5.

Enter following formula in F2 and drag down -

=IF(COUNTIF($B$2:$B$100,">="&LARGE(IF(FREQUENCY($B$2:$B$100,$B$2:$B$100)<>0,
$B$2:$B$100),5))>=ROWS($1:1),INDEX($A$2:$A$100,MATCH(1,INDEX(($B$2:$B$100=
LARGE($B$2:$B$100,ROWS(F$1:F1)))*(COUNTIF(F$1:F1,$A$2:$A$100)=0),),0)),"")

Enter following formula in G2 and drag down -

=IF(COUNTIF($B$2:$B$100,">="&LARGE(IF(FREQUENCY($B$2:$B$100,$B$2:$B$100)<>0,
$B$2:$B$100),5))>=ROWS($1:1),INDEX($B$2:$B$100,MATCH(1,INDEX(($B$2:$B$100=
LARGE($B$2:$B$100,ROWS(F$1:F1)))*(COUNTIF(F$1:F1,$A$2:$A$100)=0),),0)),"")

The workbook containing the solution can be downloaded from Solution - Challenge 38 - Find Top 5.

Sat 09 Apr 2016

Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher

By |Saturday, April 09th, 2016|Categories: Challenges, VBA|Tags: , , , , , , , , , , , |1 Comment

We made ourselves familiarized with Caesar's Shift in last challenge - Challenge 39 - Cryptography Challenge 1 - Caesar's Shift Cipher

That challenge was for writing a formula to perform Caesar's Shift on a single alphabet. But now, we need to have a fully functional Caesar's Shift Cipher.

The challenge before you is to write a VBA function which takes cell or text as first argument and amount of shift as second argument to generate the Cipher Text.

1

Hence, to generate answer in A2, we have called the function as =EncryptCS(A1,B2)

Note - The shift is only for the English Alphabets and case sensitive. Any other character like comma, space etc. should appear as they are.

The answer to this challenge would be published after a month i.e. on 09-May-16.