calculate average excluding both Filtered rows & 0 value in cells

[varunistician]

Hello Excel Gurus,

I am trying using =Averageif(Range, ">0") function or =Subtotal(101,range) function but unable to get the desired result.

I need to get the average of cells excluding filtered activity & excluding 0 values in container cell. Can someone help out?

For instance, If I use filter activity - "Preparing Pallets" then it will return with container values & some 0's. I need to calculate the average of container/min excluding 0 & the filtered cells.

Please let me know if you need anything else.

Thanks,
Varun

[bebo021999]

AVERAGE=SUM/COUNT

to COUNT:
=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$238)-MIN(ROW($B$2:$B$238)),,1))*($B$2:$B$238>0))

to SUM:
=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$238)-MIN(ROW($B$2:$B$238)),,1))*($B$2:$B$238>0)*$B$2:$B$238)

Then final AVERAGE formula:

Please Login or Register  to view this content.

[varunistician]

Hello bebo,

I tried using this formula but I need to take the average from Column not Row B.
I tried changing the formula from B to C but didn't bring the right values.

[bebo021999]

I tried changing the formula from B to C but didn't bring the right values.

I guess you have changed B:B inside the OFFSET also. It should not be changed, because it is filter criteria.

Please Login or Register  to view this content.