Sat 30 Jul 2016

Challenge 48 - Whether a Sentence is Pangram or Not

By |Saturday, July 30th, 2016|Categories: Challenges|Tags: , , , , , , , |1 Comment

This time, I decided to pose a challenge on English words. A Pangram is that sentence in English which uses all 26 alphabets of English language at least once. A very common example is the very famous sentence "The quick brown fox jumps over the lazy dog" which contains all alphabets of English language at least once. Below link contains many other examples of Pangram.

http://www.rinkworks.com/words/pangrams.shtml

The challenge before you is to write a formula which gives the output "Pangram" or "Not Pangram" depending upon the fact if cell A1 has a Pangram sentence or not. Apart from English alphabets, the sentence can contain blanks, dots and commas and no other characters.

The given link contains many examples for Pangram which you can use as your test cases. Some examples from the above site -

A mad boxer shot a quick, gloved jab to the jaw of his dizzy opponent.
Big Fuji waves pitch enzymed kex liquor.
We quickly seized the black axle and just saved it from going past him.

The solution to this challenge will be published after a month i.e. on 30-Aug-16.

Sat 23 Jul 2016

Excel Quiz 43 - Ribbon Quiz

By |Saturday, July 23rd, 2016|Categories: Quizzes|Tags: , , , , , , , |0 Comments

Excel Quiz 43

A quiz on excel ribbon

Sat 23 Jul 2016

Tips & Tricks 141 - How to Disable Quick Analysis Tool in Excel 2013

By |Saturday, July 23rd, 2016|Categories: Tips and Tricks|Tags: , , , , , , , , , , |0 Comments

In Excel 2013, we have a new feature called Quick Analysis Tool. If you select a range and press CTRL, you are presented with Quick Analysis Tool. It offers various commonly used functions which you can perform.

1

Few people don't like this. To disable it permanently -

File > Options > Uncheck the Red Zone

1

 

Mon 18 Jul 2016

Solution - Challenge 45 – Number of Days Passed in a Quarter

By |Monday, July 18th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 45 - Number of Days Passed in a Quarter

The formula to calculate number of days passed in a quarter is

=A1-DATE(YEAR(A1),(ROUNDUP(MONTH(A1)/3,0)-1)*3+1,1)

Sat 16 Jul 2016

Article 41 - Findings from the VLOOKUP and INDEX/MATCH shootout

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

Many articles by different experts laud the superiority of INDEX/MATCH over VLOOKUP. I decided to do a shootout myself and see whether it really makes sense to use INDEX/MATCH in place of VLOOKUP purely from the perspective of speed / time taken. I am not considering any other aspect but speed / time.

Following is the methodology to test -

1. The worksheet 100000 contains 100001 records. Column A is Numbers and column B is Text. Column C is alpha-numeric which is the result field.

(more…)

Sat 16 Jul 2016

Challenge 47 - Generate Pentagonal Series

By |Saturday, July 16th, 2016|Categories: Challenges|Tags: , , , , , , , |2 Comments

Pentagonal number series is following - 1, 5, 12, 22, 35, 51, 70, 92, 117, 145, 176....

You need to write an Excel formula which can be dragged down and generates the above sequence.

The solution to this problem will be published after a month i.e. on 16-Aug-16.

Sat 16 Jul 2016

Downloads 05 - Template 05 - Tornado Chart

By |Saturday, July 16th, 2016|Categories: Downloads|Tags: , , , , , , , , , , , , , , , , , , |0 Comments

Tornado chart is used to Sensitivity Analysis. It is one of the tools in Quantitative Risk Management phase of Risk Management. A good introduction about Tornado Chart is at https://en.wikipedia.org/wiki/Tornado_diagram

The Tornado Chart template can be download from Template 05 - Tornado Chart
(more…)

Sat 09 Jul 2016

Excel Quiz 42

By |Saturday, July 09th, 2016|Categories: Quizzes|Tags: , , , , , |0 Comments

Excel Quiz 42

A general quiz on Excel

Sat 09 Jul 2016

Tips & Tricks 140 - Multiple Hyperlinks within Excel Text Box

By |Saturday, July 09th, 2016|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

You created a Text Box and you put many words inside that say Yahoo, IBM, Microsoft etc...Now, you have given respective hyperlinks to them. But if you try to click on any hyperlink inside the text box, it will always open only one hyperlink.

It is possible to circumvent this behavior by work-around. You can execute following steps

1. Create a Text Box where you want to put all Hyperlinks.
2. Create many new Text Boxes.
3. Put the words in Text Boxes which you created in step 2 and give them Hyperlinks.
4. Drag the Text Boxes of Step 3 into Step 1 Text Box.
5. Align them properly and format them to remove borders.
6. Select all Text Boxes along with Step 1 Text Box > Page Layout > Group
7. Now, all individual Hyperlinks can be clicked separately.

 

Mon 04 Jul 2016

Solution - Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)?

By |Monday, July 04th, 2016|Categories: Solutions|Tags: , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)?

Enter the below formula in B1 as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=HYPERLINK("#Sheet2!"&ADDRESS(MATCH(1,--(MMULT(--((Sheet2!A1:J20)=A1),
TRANSPOSE(COLUMN(A1:J20)^0))>0),0)+ROW(A1)-1,MATCH(A1,OFFSET(Sheet2!A1,
MATCH(1,--(MMULT(--(A1=Sheet2!A1:J20),TRANSPOSE((COLUMN(A1:J20)^0)))>0),0)
-1,0,1,COLUMNS(A1:J20)),0)+COLUMN(A1)-1),"Take me to the Word - "&A1)

The workbook illustrating the above solution can be downloaded from Solution - Challenge 44 - Hyperlink to a cell in another worksheet by specific text (not cell number)