LinkBack URL
About LinkBacks
Can anyone tell me how I could combine these two functions?
=SUBTOTAL(1,A1:A10O) and AVERAGEIF(A1:A100,">0")
Can anyone tell me how I could combine these two functions?
=SUBTOTAL(1,A1:A10O) and AVERAGEIF(A1:A100,">0")
Try this array formula**:
=AVERAGE(IF(SUBTOTAL(9,OFFSET(A3,ROW(A3:A100)-ROW(A3),0,1))>0,A3:A100))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Tony,
I used this formula for the actual range with which I am working: {=AVERAGE(IF(SUBTOTAL(9,OFFSET(D9,ROW(D9:D209)-ROW(D9),0,1))>0,D9:D209))}
It appears to be working correctly. Does the formula look correct to you? I wasn't sure why you OFFSET by a couple of rows in your formula is why I ask.
CUFF
Last edited by CUFF; 01-27-2014 at 09:08 PM.
Tony,
I used this formula for the actual range with which I am working: {=AVERAGE(IF(SUBTOTAL(9,OFFSET(D9,ROW(D9:D209)-ROW(D9),0,1))>0,D9:D209))}
It appears to be working correctly. Does the formula look correct to you? I wasn't sure why you OFFSET by a couple of rows in your formula is why I ask.
CUFF
Yes, that's the correct syntax.
We need to use OFFSET so that we get an individual subtotal for each cell in the referenced range.
Excellent and thank you; I understand this all so much better now!
You're welcome. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks