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…)

Sun 13 Mar 2016

Article 37 - VBA - Generating Unique (Non-repeating) Random Numbers Efficiently

By |Sunday, March 13th, 2016|Categories: Articles, VBA|Tags: , , , , , , , , |0 Comments

This article is about how to generate unique or non-repeating random numbers given two limits. Hence, if lower limit is 10 and maximum limit is 100, hence, it should generate random numbers between 10 and 100.

The algorithm to ensure uniqueness is following -

1. Given lower limit and upper limit, generate all numbers and populate an array. Now, this array contains all numbers between lower limit and upper limit sequentially. Hence, in case of 10 to 100, it will contain entries 10, 11, 12, .....99,100.
2. Next is to shuffle the array randomly using Fisher Yates algorithm so that the array contains the numbers 10, 11, 12...99,100 in a random order.
(more…)

Sun 14 Feb 2016

Article 36 - VBA - User Names in Excel

By |Sunday, February 14th, 2016|Categories: Articles, VBA|Tags: , , , , , , , , |1 Comment

Many times, we are required to fetch User Names in Excel. Unfortunately, getting User Names in Excel is possible only through VBA. These are very small pieces of VBA codes and even a person not knowing VBA can make use of them by following the instructions here.

I am going to discuss the various type of User Names which we may be required to extract.

(more…)

Sat 30 Jan 2016

Article 35 - VBA - Timer Function and Overcoming Midnight Limitation of Timer Function

By |Saturday, January 30th, 2016|Categories: Articles, VBA|Tags: , , , , , , |1 Comment

The basic and classic and most popular mode to time your code is achieved through Timer function which is natively supplied by VBA. It returns a Single representing the number of seconds elapsed since midnight. Hence, your code will look like following when you want to measure the time and it gives the result in Seconds.

How Accurate is Timer Function

(more…)

Sat 16 Jan 2016

Article 34 - Show Date for a Coming or Previous Day (Say Coming Monday, Previous Friday)

By |Saturday, January 16th, 2016|Categories: Articles|Tags: , , , , , , , , , , , , , , , , , |2 Comments

I had already written about one formula where I dealt with coming day formula - Article 3 – Show Date for a Coming Day (e.g. Coming Saturday)

I wanted to revisit this with following things in mind -

1. I wanted to explore various formulas on the above topic. Here, I am giving 2 additional formulas. So, I will be talking about a total of 3 formulas.

2. In Article 3, I talked only about Coming Day not the Previous Day. In this article, I will be talking about formulas for Previous Day also.

(more…)

Sat 02 Jan 2016

Article 33 - Rank when Duplicates Exist (Ties)

By |Saturday, January 02nd, 2016|Categories: Articles|Tags: , , , , , |1 Comment

Ranking when duplicates (ties) exist is an interesting problem and you will be called upon to make choices when duplicates exist and you have to rank them. Suppose, you have the data like below. 2 rows are formatted in Yellow and 3 rows are formatted in Green to demonstrate the existence of duplicates.

1

(more…)

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…)

Sat 05 Dec 2015

Article 31 - Slab Billing - Calculate Income Tax, Electricity (Utility) Bills based on Slabs

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

You will encounter slab billings in two very common documents - One is Electricity / Utility and another one is Income Tax. If you see your electricity bills, you will notice following type of entries (values are for illustration purposes only, please do not attach any meaning to them)

0 - 50 Units - $1.5 per Unit
51-100 Units - $ 2.0 per Unit
101-500 Units - $3.5 Per Unit
501 - 2000 Units - $6 Per Unit
2000 Unit Onwards - $9 Per Unit

It means that your first 50 units will be charged at $1.5 per unit, next 50 will be charged at $2.0 per unit and next 400 units will be charged at $3.5 per unit, next 1500 units will charged at $6 per unit and any unit after 2000 will be charged at $ 9 per unit.

(more…)

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 07 Nov 2015

Article 29 - Traffic Lights Conditional Formatting for Project Status for Text R / A / G

By |Saturday, November 07th, 2015|Categories: Articles, VBA|Tags: , , , , , , , , |1 Comment

We all know that Traffic Lights are great visuals to communicate Project Status. But, we also know that Conditional Formatting for Traffic Lights can be done only through Numbers not through Text.

We need a flexible system that if I enter R (for Red), A (for Amber) and G (for Green), the required Traffic Light should be displayed. (A for Amber can be substituted with Y for Yellow also). Below is a method to achieve the required Traffic Lights.

(more…)