At the bottom of the Table I am trying to Average the columns. But I would like to ignore the zeros as well and I need this to work when filtering.
So basically I would like to combine these two formulas to get what I want.
=SUBTOTAL(101,[Column 13])
=AVERAGE(IF(L6:L94>0,L6:L94))
Thank you!
Try this Array Formula, with CTRL+SHIFT+ENTER
=AVERAGE(IF(SUBTOTAL(2,OFFSET(L6,ROW(L6:L94)-ROW(L6),)),IF(L6:L94>0,L6:L94)))
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
try using =averageif() or =averageifS()
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks