Sat 28 Nov 2015

Excel Quiz 26

By |Saturday, November 28th, 2015|Categories: Quizzes|Tags: , , , |1 Comment

Excel Quiz 26

A general quiz on Excel

Sat 28 Nov 2015

Tips & Tricks 120 - Filter by Color Drop Down Menu Greyed Out Problem

By |Saturday, November 28th, 2015|Categories: Tips and Tricks|Tags: , , , , , |1 Comment

Sometimes, you find that though your are having colored cells but Filter by Color is greyed out i.e. it can not be selected.

Following are the possible causes -

1. Your sheets may be grouped. If they are grouped, you will notice word "Group" in the title bar of your Excel workbook.

1

Another way to check whether the sheets are grouped or not is to right click on any tab name and option "Ungroup Sheets" will appear:

1

2. There may be blank lines before a color is encountered in your range. Filter doesn't like blank cells. Hence, be watchful.

3. The workbook may be shared. If this is a shared workbooks, the word "Shared" will appear in the title bar of your workbook.

1

To remove sharing > Review tab > Share Workbook > Uncheck the box in Red zone

1

4. Also if first colored cell appears after approximately 10000 rows, Excel may not detect the color. To overcome this behavior, put a color in the first cell and now it will enable the Filter by Color. Only thing is that this colored cell will be extra in your filtered list.

Sat 28 Nov 2015

Tips & Tricks 119 – Numerology Sum of the Digits aka Sum the Digits till the result is a single digit

By |Saturday, November 28th, 2015|Categories: Tips and Tricks|Tags: , , , , , , |2 Comments

In Numerology, it is often a task to add the digits till the result is a single digit. For example, 74 = 7 + 4 = 11 = 1 + 1 = 2

23 = 2 + 3 = 5

78 = 7 + 8 = 15 = 1 + 5 = 6

1234567 = 1 + 2 + 3 + 4 + 5 + 6 + 7 = 28 = 2+ 8 = 10 = 1+ 0 = 1

The formula to achieve the same is

=MOD(A1-1,9)+1

Mon 23 Nov 2015

Solution - Challenge 28 – Create Pascal’s Triangle

By |Monday, November 23rd, 2015|Categories: Solutions|Tags: , , , , , , , , |1 Comment

Below is a possible solution to the Challenge 28 – Create Pascal’s Triangle.

Put 1 in L2 and put following formula in C2 and drag right and down.

=IF(IF(B2="",0,B2)+IF(D2="",0,D2)=0,"",IF(B2="",0,B2)+IF(D2="",0,D2))

This will generate following Pascal's Triangle.

1

To make it better looking, you may use Conditional Formatting.

Select C2 and U11 > Home tab > Conditional Formatting > New Rule > Use a formula to determine which cells to format and put following formula and choose appropriate Fill Colour

=AND(ISNUMBER(B2),ISNUMBER(D2))

This will generate the following Pascal's Triangle

1

A workbook illustrating the solution can be downloaded from Pascal Triange Solution

Sat 21 Nov 2015

Article 30 - VBA - Approaches for Unique Count and Time Performance Results for the Same

By |Saturday, November 21st, 2015|Categories: Articles, VBA|0 Comments

As part of this article, we will look into various approaches for counting unique in VBA and also see what time do they take to determine the best approach on the basis of "Time Taken". I have used Charles William's MicroTimer for timing the time.

https://msdn.microsoft.com/en-us/library/aa730921%28v=office.12%29.aspx

We will see performance of these approaches with following number of records

- 100000
- 50000
- 33000
- 10000
- 1000
- 100

(more…)

Sat 21 Nov 2015

Challenge 30 - Average Last 5 Numbers in a Range

By |Saturday, November 21st, 2015|Categories: Challenges|Tags: , , , , , , , , , |1 Comment

The Excel file related to this challenge can be downloaded from Challenge - Average Last 5 Numbers

Let's say that you have got a range like this. The range which can contain values is A1:A100. The problem is to pick up last 5  numbers and average them. Notice that there are blanks and non numbers also. You need to pick only numbers. In this case, you need to average 2,44,9,26,4, hence answer would be 17.

Note, sometimes range can have less than 5 numbers also, hence formula should take care of this requirement also. Suppose there are only 4 numbers, in that case average should be for those 4 numbers only. Also, entire range can have no numbers. In that case, result should be 0.

1

You may post your answer in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 20-Dec-15.

Sat 14 Nov 2015

Tips & Tricks 118 - Stop Auto Creation to Hyperlinks

By |Saturday, November 14th, 2015|Categories: Tips and Tricks|Tags: , , , , |0 Comments

We all know that if you enter a web address like www.microsoft.com, http://www.microsoft.com or even e mail address like admin@eforexcel.com, Excel converts these to Hyperlinks which can be clicked. Sometimes, we don't want this behaviour and we want to stop this -

Trick 1 - After entering a web address / e mail address, press CTRL+Z. The Hyperlink will get removed.

Trick 2 - Enter the web address / e mail address with a leading apostrophe

Trick 3 - File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type tab and uncheck "Internet and network paths with hyperlinks"

1

Sat 14 Nov 2015

Tips & Tricks 117 - VBA - How to Count a particular character in a String

By |Saturday, November 14th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , |0 Comments

When we use formulas inside Excel, we have following stock formula to count the number of times a character appears in a string -

=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",""))

In this, we are trying to count the occurrence of character "a" in A1. Let's assume that A1 = "Abraham Arthurway". Hence, the answer which we would get will be 5.

To count the same in a range of cells -

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),"a","")))

Now, how to do the same thing in VBA. In VBA, you can use following expression to count this -

UBound(Split(LCase(Range("A1")), "a"))

To count the same in a range of cells -

UBound(Split(LCase(Join(WorksheetFunction.Transpose(Range("A1:A10")))), "a"))

Sat 14 Nov 2015

Excel Quiz 25

By |Saturday, November 14th, 2015|Categories: Quizzes|Tags: , , , , , |0 Comments

Excel Quiz 25

A general quiz on Excel.

Mon 09 Nov 2015

Solution - Challenge 27 – Count the Number of Alphabets and Numerals and Other Characters

By |Monday, November 09th, 2015|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 27 – Count the Number of Alphabets and Numerals and Other Characters.

The formula for counting number of alphabets

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(65:90)),"")))

The formula for counting number of numerals

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,"")))

The formula for counting other characters

=LEN(SUBSTITUTE(A1," ",""))-C1-C2

Where C and C2 are number of alphabets and numerals.