Sun 27 Dec 2015

Excel Quiz 28 - LOOKUPs - Part II

By |Sunday, December 27th, 2015|Categories: Quizzes|Tags: , , , , , |0 Comments

Excel Quiz 28 - LOOKUPs - Part II

Part II quiz on LOOKUPs in Excel

Sat 26 Dec 2015

Tips & Tricks 124 - VBA - Check if a Range is Blank When Range Contains Formulas returning Blanks

By |Saturday, December 26th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , , |0 Comments

In VBA, often, we have situations where we need to check if a range is blank or not. The popular formula which we see following is used to check whether range is blank or not.

WorksheetFunction.CountA(Range("A1:A100"))

But if your range contains even a single formula which is returning blank, the above will not return the range as blank even though it contains literal blanks.

To handle this, you can use below to check if your range is blank even though the range contains formulas returning blanks.

Range("A1:A100").Rows.Count = WorksheetFunction.CountBlank(Range("A1:A100"))

 

Sat 26 Dec 2015

Tips & Tricks 123 - Change the Color of Border Lines of Active Cell

By |Saturday, December 26th, 2015|Categories: Tips and Tricks|Tags: , , , , , , |1 Comment

Notice the color of Active Cell which is Green. Few people may like some other color.

1

 

 

 

 

 

Excel doesn't provide a native facility to change this. But Chip Pearson offers an Add-in which provides this functionality and has many custom options also.

http://www.cpearson.com/excel/RowLiner.htm

Sun 20 Dec 2015

Solution - Challenge 30 – Average Last 5 Numbers in a Range

By |Sunday, December 20th, 2015|Categories: Solutions|Tags: , , , , , , , , , |0 Comments

Below is a possible solution to Challenge 30 – Average Last 5 Numbers in a Range.

Use below formula -

=IFERROR(AVERAGE(INDIRECT("A"&LARGE(INDEX(ISNUMBER(A1:A100)*
(ROW(A1:A100)),,),MIN(COUNT(A1:A100),5))&":"&"A"&LARGE(
INDEX(ISNUMBER(A1:A100)*(ROW(A1:A100)),,),1))),0)

The workbook containing this solution is uploaded to Solution - Challenge 30 – Average Last 5 Numbers in a Range

Sat 19 Dec 2015

Challenge 32 - Convert Matrix into Linear Column - I

By |Saturday, December 19th, 2015|Categories: Challenges|Tags: , , , , , |2 Comments

Suppose you have been given a grid like below. The challenge before you is to write a formula to create a linear column with no blanks in between.

The column ranges would be A to D whereas row ranges would be from 1 to 100. Hence, your formula has to be flexible enough to consider A1:D100 range. (Note - There would be no blanks in between the values in the Grid)

Download the workbook related to this from Challenge 32 - Convert Matrix into Linear Column

1

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

Sat 19 Dec 2015

Article 32 - Calculate Working Hours between Two Ranges and Exclude Weekends and Holidays (SLA Calculation)

By |Saturday, December 19th, 2015|Categories: Articles|Tags: , , , , , , , |0 Comments

Suppose you have two time stamps say A1: 23-Dec-2015 09:15 AM and B1: 29-Dec-2015 02:30 PM and say your working hours are between 09:00 AM to 05:00 PM. You have been tasked to calculate the working hours between these two dates and you need to exclude weekends (here - 26-Dec-15 and 27-Dec-15) and any holiday (here - 25-Dec-15). Below is the detail calculation for this -

1

(more…)

Sun 13 Dec 2015

Excel Quiz 27 - Crossword VI

By |Sunday, December 13th, 2015|Categories: Quizzes|Tags: , , |0 Comments

Time for Crossword Again. This crossword is interactive and you can use browser to play with this. Click in the Grid to start.

Click on Check Puzzle just above Questions to check your score.

If you want to print the Crossword on a paper to play with, download from here Excel Crossword 6

The answer key can be downloaded from here Excel Crossword 6 Answers

If the below Crossword is not properly visible because of WordPress plugin limitation, you can play it properly here  Excel_CW_6

(more…)

Sat 12 Dec 2015

Tips & Tricks 122 - Always Open a Specific Worksheet when Workbook is Opened

By |Saturday, December 12th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

This can only be accomplished through VBA.

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double click on ThisWorkbook
5. Put following code in ThisWorkbook (Replace Sheet Name appropriately)

 

Sat 12 Dec 2015

Tips & Tricks 121 - Macro to Protect / Un-protect All or Selective Sheets

By |Saturday, December 12th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

There may be scenarios where you want the facility to protect or unprotect all sheets in one go with added option to choose sheets where to perform this operation. Below is the code to perform this.

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the below code in this
6. ALT+F8 to display Macro Window
7. Run your Macro from here

'******* Protect / Un-protect Sheets *******

 

Sun 06 Dec 2015

Solution - Challenge 29 – Reverse (Flip) a Number String

By |Sunday, December 06th, 2015|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 29 – Reverse (Flip) a Number String

You may use below formula to reverse a number string -

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
*10^ROW(INDIRECT("1:"&LEN(A1)))/10)