Sun 28 Oct 2018

Article 48 - Show Pictures Conditionally

By |Sunday, October 28th, 2018|Categories: Articles|Tags: , , , |0 Comments

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.