Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

SubTotal with AvaeageIF

Hello All!,
 
Attached please find my spreadsheet.

As you can see I needed subtotals that can be filtered, so I used the subtotal() function.
Unfortunately, some of my rows will sum to Zero (ex: ID 10 and ID 20)
...but since: =SUBTOTAL(1,G2:G30)
    ...will include the zeros, this throws off my average

I know I can also use the newer AverageIF function, but I am not quite sure if I can adapt this to work if the list is filtered.

In other words, how can I get a filtered average for each Customer, that does not include the zero values?

If anything here is unclear, please let me know...
Thanks

JeffCoachman
Calc-with-Zeros.xlsx
0
Jeffrey Coachman
Asked:
Jeffrey Coachman
  • 9
  • 6
  • 3
3 Solutions
 
NBVCCommented:
Try:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(G2:G30,ROW(G2:G30)-MIN(ROW(G2:G30)),,1)),--(F2:F30<>0))/SUMPRODUCT(SUBTOTAL(2,OFFSET(G2:G30,ROW(G2:G30)-MIN(ROW(G2:G30)),,1)),--(F2:F30<>0))
0
 
Jeffrey CoachmanMIS LiasonAuthor Commented:
Oh
My
God....
;-)

OK, ...I'll give it a try...
0
 
NBVCCommented:
Actually, not sure what I was thinking.....

give this a try instead:

=AVERAGE(IF(F2:F30<>0,G2:G30))

confirmed with CTRL+SHIFT+ENTER not just ENTER.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jeffrey CoachmanMIS LiasonAuthor Commented:
Thanks NB_VC  !

<Actually, not sure what I was thinking.....>
Whatever you were thinking, ...it seemed to work OK...
;-)

One question though before I try the new formula...
In your original formula, you used an double minus sign ("--")

SUMPRODUCT(SUBTOTAL(9,OFFSET(G2:G30,ROW(G2:G30)-MIN(ROW(G2:G30)),,1)),--(F2:F30<>0))/SUMPRODUCT(SUBTOTAL(2,OFFSET(G2:G30,ROW(G2:G30)-MIN(ROW(G2:G30)),,1)),--(F2:F30<>0))

...is this correct?, and what does the double negative do?

Trying the new formula now...

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonAuthor Commented:
The new formula does not update when the list is filtered...?
0
 
NBVCCommented:
No the new one cannot be used for filtering... I guess that is what I was assuming at first, but thought maybe not... guess I was right initially.
0
 
NBVCCommented:
Yes your formula looks correct.

the -- is too coerce TRUE/FALSE results to 1/0 results, respectively so that the arithmetic can take place.
0
 
barry houdiniCommented:
You could also use a combination of NB_VC's two suggestions, an array formula with AVERAGE that only considers non-zero rows like this

=AVERAGE(IF(F2:F30<>0,IF(SUBTOTAL(2,OFFSET(G2,ROW(G2:G30)-ROW(G2),0)),G2:G30)))

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 
Jeffrey CoachmanMIS LiasonAuthor Commented:
NB_VC,
Thanks for the info...
0
 
Jeffrey CoachmanMIS LiasonAuthor Commented:
barryhoudini,

NB_VC's first formula seems to still be working, but I will try yours as well, and report back.
0
 
barry houdiniCommented:
Yes, I would expect both to give the same answers - my suggestion has the advantage of being a little shorter........but the disadvantage of requiring "array entry"......

regards, barry
0
 
Jeffrey CoachmanMIS LiasonAuthor Commented:
Ok,
Both NB_VC's fist post, and barryhoudini's submission are both working now.
(Sub"Averaging" correctly while filtering.

Are you guys cool with a 50/50 split...?

Thanks again to you both.

;-)

JeffCoachman
0
 
NBVCCommented:
I am okay with that.  Thanks.
0
 
barry houdiniCommented:
Hey, Jeff!

>Are you guys cool with a 50/50 split...?

That's entirely up to you - NB_VC proposed a good working solution before me so I'd suggest you give him the "Lion's share"

regards, barry
0
 
Jeffrey CoachmanMIS LiasonAuthor Commented:
From years of answering question in the Access zone, I know that most top pros, like yourselves, don't sweat points really.

You both gave working solutions, so a split it is.
;-)

Again, thanks to both of you for taking the time to help me out...

;-)

JeffCoachman
0
 
NBVCCommented:
does closing request assign points, mods?  Just curious.
0
 
Jeffrey CoachmanMIS LiasonAuthor Commented:
yes
0
 
Jeffrey CoachmanMIS LiasonAuthor Commented:
Thanks!
;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 9
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now