Please consider supporting this website by buying me a coffee.
Thank you for your consideration!
Updated | November 13, 2024
Average the Last 3 Values Greater than Zero
The following formula returns the average of the last 3 values greater than zero in B3:B12...
=AVERAGE(TAKE(FILTER(B3:B12,B3:B12>0),-3))
For earlier versions of Excel...
=AVERAGE(IF(ROW(B3:B12)>=LARGE(IF(B3:B12>0,ROW(B3:B12)),3),IF(B3:B12>0,B3:B12)))
Note that the formula needs to be confirmed with CTRL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
Based on the sample data, the formula returns 42.33.
Sample Workbook: Download