Forum Discussion

null null's avatar
null null
Copper Contributor
Oct 03, 2018

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?

Excel
Excel on Mac
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Oct 03, 2018

    Hi

     

    Something like this.

    =AVERAGE(B3:INDEX(3:3,AGGREGATE(15,6,COLUMN(B3:M3)/(B3:M3>0),6)))

     

Resources