Below is a possible solution to Challenge 30 – Average Last 5 Numbers in a Range.
Use below formula -
The workbook containing this solution is uploaded to Solution - Challenge 30 – Average Last 5 Numbers in a Range
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.
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.