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

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.

Sat 26 Sep 2015

Challenge 26 - Find Sum given multiple OR conditions across columns

By |Saturday, September 26th, 2015|Categories: Challenges|Tags: , , , , |1 Comment

-- The Excel file related to this challenge can be downloaded from Challenge - Multiple OR Conditions ---

Given your data below, you need to find the total sales for cities given in F1 to I1 and zones given in F2 to I2. Either city has to match in F1 to I1 or zone should match in F2 to I2. If both city and zone match, it should be considered once only not twice.

Seattle East 20
Seatltle West 10
Miami East 30
Denver South 50

The answer for above for Seattle and East should be 20+10+30 = 60 not 20+20+10+30 = 80

Multiple OR Conditions

You may post your answer in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 25-Oct-15.