Subtotals (average) working with blank cells

[jacobsens]

Hi Folks
Trying to create a Subtotal average percentage. So we get new averages based on different filtered criteria. Many of the cells are blank. How ca we generate the subtotal average and ignore those blank/zero cells? My current formula is as follows =SUBTOTAL(101,J7,J8766). Totally appreciate the help!

 

[arthurbr]

Perhaps = AGGREGATE(9,5,j7:j8766)/AGGREGATE(3,5,J7:J8766)

 

[admiral100]

Hi,

Try this
Control+Shift+Enter not just Enter

=AVERAGE(IF(SUBTOTAL(109,OFFSET(J7:J8766,ROW(J7:J8766)-ROW(J7),0,1))>0,J7:J8766))

 

[Aladin Akyurek]

To ignore zero's while under filter, control+shift+enter, not just enter:

=AVERAGE(IF(SUBTOTAL(2,OFFSET(J7,ROW(J7:J8766)-ROW(J7),0,1)),IF(ISNUMBER(1/J7:J8766),IF(J7:J8766>0,J7:J8766))))

 

[Jonmo1]

I don't understand....

The Subtotal(101 function will already ignore blanks by design..