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 18 Jun 2016

Challenge 45 - Number of Days Passed in a Quarter

By |Saturday, June 18th, 2016|Categories: Challenges|Tags: , , , , , , |1 Comment

This time the challenge is - If a date is given, what would be the formula to find the number of days passed in a quarter.

If A1 has the value 12-Mar-16, then 31 days in Jan, 29 days in Feb (2016 is a leap year) and 11 days in Mar = 31+29+11 = 71 days have passed in Q1. Hence, answer is 71.

If A1 has 15-Apr-16, then 14 days have passed in Q2. Hence, answer is 14.

If A1 has 28-Aug-16, then, 31 days in Jul and 27 days in Aug = 31+27 = 58 days have passed in Q3. Hence, answer is 58.

You need to give the formula to find the above.

The solution to above challenge will be published after a month i.e. on 18-Jul-16.

Sat 29 Nov 2014

Article 8 - Calculating Percentage Change between New and Old Value

By |Saturday, November 29th, 2014|Categories: Articles|Tags: , , , , , |2 Comments

Many times, I get inspiration to write about articles when I post responses to questions on Microsoft Community. The inspiration to write about this has come from following post - Percentages

You will also have many occasions particularly when you are asked to compute percentage change between two values. This is very much encountered in Finance industry where you have to report percentage growth for many parameters like revenue, cost etc. Economics has demand growth, supply growth and every other industry has their own growth metric. Telecom will have subscriber growth, ARPU growth and so on.

Note - The article is not about growth between percentage values i.e. it is not about growth between 20% and 30% but between absolute values.

(more…)

Sat 28 Jun 2014

Tips & Tricks 25 - Extract Integer and Decimal Portion of a Number

By |Saturday, June 28th, 2014|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

To extract Integer portion, one of the below can be used -

=INT(A1)
=TRUNC(A1)

Positive value in A1 - If A1 contains 84.65, then answer would be 84.
Negative value in A1 - If A1 contains -24.39, then answer would be -24.

If you want only +ve value whether value in A1 is -ve or +ve, the formula can have many variants.

=INT(A1)*SIGN(A1)  OR =TRUNC(A1)*SIGN(A1)
=INT(ABS(A1)) OR =TRUNC(ABS(A1))
=ABS(INT(A1)) OR = ABS(TRUNC(A1))

To extract Decimal portion -

=MOD(ABS(A1),1)
=ABS(A1)-INT(ABS(A1))

Positive value in A1 - If A1 contains 84.65, then answer would be 0.65.
Negative value in A1 - If A1 contains -24.39, then answer would be 0.39.