Sun 10 Sep 2017

Challenge 65 - How Many Sundays on Last Date of Months

By |Sunday, September 10th, 2017|Categories: Challenges|Tags: , , , |2 Comments

This time you need to work out the number of Sundays on last date of the months between two given dates. For example, between two dates of 1-Jan-2017 to 31-Dec-2020, total number of Sundays on last date of the months is 6 as highlighted below.

The answer to the solution will be published after a month i.e. on 10-Oct-17.

Tue 15 Aug 2017

Solution - Challenge 64 - Sum up the Range where a particular alphabet appears

By |Tuesday, August 15th, 2017|Categories: Solutions|Tags: , , |0 Comments

Below is a possible solution to the Challenge 64 - Sum up the Range where a particular alphabet appears

=SUMPRODUCT(ISNUMBER(SEARCH(" "&C2&","," "&A2:A13&","))*(B2:B13))

 

 

Mon 23 May 2016

Solution - Challenge 41 – Sum the Maximum Number where duplicates Exist

By |Monday, May 23rd, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 41 – Sum the Maximum Number where duplicates Exist

Enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=SUM((MATCH(A2:A10,IF(COUNTIFS(A2:A10,A2:A10,B2:B10,">"&B2:B10)=0,
A2:A10),0)=ROW(A2:A10)-MIN(ROW(A2:A10))+1)*B2:B10)

The workbook illustrating the above solution can be downloaded from Solution - Challenge 41 - Sum the Maximum Number where duplicates Exist

Sat 23 Apr 2016

Challenge 41 - Sum the Maximum Number where duplicates Exist

By |Saturday, April 23rd, 2016|Categories: Challenges|Tags: , , , , , |1 Comment

Suppose, you have been given following and you will need to find duplicates in column A and sum the maximum values from column B. If duplicates don't exist, values will be taken as they are. The values which needs to be summed up are colored. The answer would be 123 in this.

1

The workbook related to this challenge can be downloaded from Challenge 41 - Sum the Maximum Number where duplicates Exist

The answer to this challenge would be published after a month i.e. on 23-May-16.

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

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

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.

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.

Sun 25 Oct 2015

Solution - Challenge 26 – Find Sum given multiple OR conditions across columns

By |Sunday, October 25th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the challenge Challenge 26 – Find Sum given multiple OR conditions across columns.

Put following formula for the result -

=SUMPRODUCT((COUNTIF($F$1:$I$1,$A$2:$A$20)+
COUNTIF($F$2:$I$2,$B$2:$B$20)>0)*($C$2:$C$20))

The workbook illustrating the solution can be downloaded from Solution - Challenge 26 – Find Sum given multiple OR conditions across columns.