Forum Discussion
null null
Oct 03, 2018Copper Contributor
Average of first n numbers ignoring blank cells
I want to average the first n numbers in a row and have the blank cells ignored. e.g on the attached file I want to average Kate's first 6 numbers and have the 7th number, 57.08, not counted, but I want to use the same formula for Lester who's 7th number is in a different column and who has blank spaces in different places. Then when I add more percentages in the next date, 9-10-18 I want it to be counted as one of the first 6. I realise I will have to change the range in the formula but everything else should be the same. Can anyone help me please?
- Detlef_LewinSilver Contributor
Hi
Something like this.
=AVERAGE(B3:INDEX(3:3,AGGREGATE(15,6,COLUMN(B3:M3)/(B3:M3>0),6)))