Average 3 last values >0 formula help

[greykitten]

Hello,
A recent Chandoo post on formula forensics asked the question of how to write a formula that will average the last 3 values larger than zero.

(http://chandoo.org/wp/2013/10/03/formula-forensics-no-035-average-the-last-3-values-greater-than-0/

I followed the sample file and tried extending the array to the last 5 values with: =AVERAGEIFS($B$3:F3,$B$2:F2,LARGE(IF($B$3:F3>0,$B$2:F2),{1,2,3,4,5})) but it gave me a DIV/0! error. Anyone know why?

Also, why do the instructions say to press F2 and then F9? Nothing happened when I did that in the sample file.

 

[Luke M]

Your formula is missing a function, the outer AVERAGE. Should be:
=AVERAGE(AVERAGEIFS($B$3:F3,$B$2:F2,LARGE(IF($B$3:F3>0,$B$2:F2),{1,2,3,4,5})))

Make sure that there are actually 5 numbers greater than 0 in your range, and that you confirm the formula with Ctrl+Shift+Enter.

As for the F2 and F9, F2 is to edit the formula. Select a cell with a formula, hit F2, and you'll start editing the formula (or cell contents). Then, if you select part of a formula and hit F9, that portion will be evaluated/calculated.