About eforexcel

This author has not yet filled in any details.
So far eforexcel has created 399 blog entries.
Sat 24 Jun 2017

Excel Quiz 53

By |Saturday, June 24th, 2017|Categories: Quizzes|Tags: , , , , , , , , , , , , , , , , , , |0 Comments

Excel Quiz 53 - A Quiz on Data Tab in Excel

This quiz checks your knowledge about Data tab in Excel.

Sat 17 Jun 2017

VBA - A Function to Check whether a Given Number is Prime

By |Saturday, June 17th, 2017|Categories: VBA|Tags: , , , , , , , , , |0 Comments

Below is an optimized function to check on the primality of a number. This function takes input number as Double, hence can be used to check upto a number having 15 significant digits. Whereas Long can take up to a maximum of 10 significant digits and maximum number it can support is 2,147,483,648.

First function is when pure number is passed, hence argument can be declared as Double. Hence, you will have to pass the value not the range.

Second function is when variant is passed as argument so that even range can be passed in this function.

Below is time performance for both functions -

The file used for checking time performance - Prime Number Checker

Tue 13 Jun 2017

Solution 62 - Produce the Sum for Merged Cells Headers

By |Tuesday, June 13th, 2017|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution for the challenge - Challenge 62 - Produce the Sum for Merged Cells Headers

Put following formula B14 and drag right and down

=SUM(OFFSET($A$1,ROWS($1:1),MATCH(B$13,$1:$1,0)-1,,IFERROR(MATCH(C$13,$1:$1,0),COUNTA($2:$2)+1)-MATCH(B$13,$1:$1,0)))

Sat 10 Jun 2017

Challenge 63 - Convert to Date Format

By |Saturday, June 10th, 2017|Categories: Challenges|Tags: , , , , , , , , , , , |0 Comments

This time, you have been given a file containing Text dates into various formats. The challenge before you is to write a single formula to convert them into an appropriate date format.

The challenge file can be downloaded from Challenge 63 - Convert to Date Format

1

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

Sat 03 Jun 2017

Tips & Tricks 160 - When is Labor Day in a Given Year

By |Saturday, June 03rd, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

While 1st May is celebrated as Labour Day in most of the countries but in USA/Canada, it is celebrated on 1st Monday of September.

Suppose the year is given in cell A1, you can use following formula to calculate the date for Labor Day

=CEILING(DATE(A1,9,1)-2,7)+2

Note - This utilizes the knowledge gained in Article 34 - Show Date for a Coming or Previous Day (Say Coming Monday, Previous Friday)

1

Sat 27 May 2017

Excel Quiz 52

By |Saturday, May 27th, 2017|Categories: Quizzes|Tags: , , , , , , , , , , |0 Comments

Excel Quiz 52 - A Quiz on Formulas Tab in Excel

This quiz checks your knowledge about Formulas tab.

Sat 20 May 2017

Article 45 - How to know if a Cell contains Time

By |Saturday, May 20th, 2017|Categories: Articles, VBA|Tags: , , , |0 Comments

We talked about Article 19 – How to Check if a cell contains a date. Now, in this article, I will talk about checking the cell for time.

The way Excel stores dates as numbers, in the same way it stores time as numbers only. Only change is in range. The numbers get stored between 0 to 1. Hence,  6 AM is stored as .25, 12 PM is stored as 0.5 and so on.

(more…)

Sat 13 May 2017

Challenge 62 - Produce the Sum for Merged Cells Headers

By |Saturday, May 13th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Download the workbook related to this challenge - Sum for Merged Cells

This time challenge is to produce the sum where header is merged cells. For the layout like below -

1

You need to write a single formula which can be dragged right and down to generate the sum for below table

1

The solution to this challenge will be published after a month i.e. on 13-Jun-17.

Sat 06 May 2017

Downloads 15 - Excel Formulas Bible

By |Saturday, May 06th, 2017|Categories: Downloads|Tags: , , , , , , , , , , , , |0 Comments

This is one single document which contains close to 100 formulas dealing with various situations. Useful for Intermediate and Advanced users.

Download it from Excel - Formulas Bible

Mon 01 May 2017

Solution - Challenge 61 - Generate Multiplication Table

By |Monday, May 01st, 2017|Categories: Solutions|Tags: , , , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 61 - Generate Multiplication Table

Put following formula and drag right and down -

=ROWS($1:1)*COLUMNS($A:A)