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

 

 

Sat 15 Jul 2017

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

By |Saturday, July 15th, 2017|Categories: Challenges|Tags: , , , |1 Comment

Suppose, you have been given a range like this and you need to find the sum of column B where the alphabet "c" appears alone.

To check your answer, the sum will be 27 for above.

The file related to this challenge can be downloaded from Challenge 64 - Sum up the Range where a particular alphabet appears

The answer to the above solution will be presented after a month i.e. on 15-Aug-17.

Sat 11 Mar 2017

Excel Quiz 50

By |Saturday, March 11th, 2017|Categories: Quizzes|Tags: , , , , |0 Comments

Excel Quiz 50 - A Quiz on Insert Tab in Excel

This checks your knowledge on Insert tab in Excel.

Sat 25 Feb 2017

Challenge 60 - Generate a list of 2nd and 4th Saturdays for a Year

By |Saturday, February 25th, 2017|Categories: Challenges|Tags: , , , , , , , |1 Comment

In India, 2nd and 4th Saturdays are very important days as on these days, the banks remain closed. Hence, banks in India are closed on all Sundays and 2nd and 4th Saturdays apart from their holiday calendar.

To calculate Networkdays for banking industry in India needs that the list of 2nd and 4th Saturdays needs to be generated. You know the syntax of NETWORKDAYS -

NETWORKDAYS.INTL(Start_Date, End_Date, [Weekend],[Holidays])

In weekend parameter, we can specify Sunday as weekend and in Holidays list, we can put the holidays and also the 2nd and 4th Saturdays.

Now, the challenge before you is to write a formula which takes Year as Input from A1 and which when dragged down produces the 2nd and 4th Saturdays.

1

Note - The solution to above problem will be published after a month i.e. on 25-Mar-17.

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.

Tue 14 Jul 2015

Solution - Challenge 15 – Floyd’s Triangle

By |Tuesday, July 14th, 2015|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a proposed solution for the challenge Challenge 15 – Floyd’s Triangle

Put this formula anywhere in your sheet and drag right and down

=IF(COLUMNS($A:A)>ROWS($1:1),"",IF(ROWS($1:1)=1,1,ROWS($1:1)-2+
COLUMNS($A:A)+INDIRECT(ADDRESS(ROW()-1,COLUMN()-COLUMNS($A:A)+1,1))))

The solution workbook is located at Solution - Challenge 15 – Floyd’s Triangle

Mon 13 Jul 2015

Solution - Challenge 4 – SUM of Multiplication of Preceding Digits by Succeeding Digits

By |Monday, July 13th, 2015|Categories: Solutions|Tags: , , , , , , , , , , |0 Comments

Below is a possible solution to the challenge -Challenge 4 – SUM of Multiplication of Preceding Digits by Succeeding Digits

Given your numerical string is in A1, use following formula -

=IFERROR(SUMPRODUCT((MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),1)*MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))),0)

The answer would be as follows

Sat 13 Jun 2015

Challenge 18 - Find the Longest Word in a List

By |Saturday, June 13th, 2015|Categories: Challenges|Tags: , , , |1 Comment

--- The worksheet related to this problem can be found at Longest Word ---

Suppose you have a list of words in A1 to A10. The challenge before you is to write a formula (preferably non-array) to find the longest word in the list.

In case of ties, pick up the first longest word.

Longest Word

The response can be posted in the comments section of this post.

Sat 06 Jun 2015

Challenge 17 – Sum if Y Appears More than One Time Consecutively Maximum

By |Saturday, June 06th, 2015|Categories: Challenges|Tags: , , , , , , |2 Comments

The Excel file related to this problem can be downloaded from Challenge - Most Consecutive Appearance

Suppose you have a table like below. The challenge before you is to find the sum of prize money if Y appears consecutively more than once for a person maximum time. You need to input name in F2 and sum of prize money should appear in F3. If name doesn't meet the criterion of consecutive Y maximum times, then Prize money should be blanks or any message can be put here.

Taking the example of Smith, most consecutive times Y appears in rows 7 to 10. For Doe, rows 15 to 16 and for Liz 19 to 21. Note for Smith, Y appears consecutively 3 times, first time in rows 2 and 3, second time in rows 7 to 10 and third time in rows 12 to 13. But since, we are talking about maximum consecutive appearance, hence we need to consider row 7 to 10.

If their are ties, pick up the first maximum consecutive appearance.

Note - Without the use of a helper column, it may be very difficult to solve. Hence, please feel free to use a helper column. Maximum allowed helper column is 0.

1

Note - You may like to post your answer to this in comments.

Sat 30 May 2015

Challenge 16 – Team Pairing

By |Saturday, May 30th, 2015|Categories: Challenges|Tags: , , , , , , |1 Comment

Given the team list below, the challenge before you is to align teams RANDOMLY under Group A and Group B where one team faces another and there is no repetitions of the team. The formula should be flexible, in case, team list increases / decreases. The team list will always be even as if number of teams is odd, then one team will be left out.

The related Excel file can be downloaded from here Team Pairing

11

Note - You may choose to post your response in comments.