Solved

SubTotal with AvaeageIF

Posted on 2013-06-12
18
261 Views
Last Modified: 2013-06-17
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
Comment
Question by:Jeffrey Coachman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 3
18 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 250 total points
ID: 39241626
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
 
LVL 74

Author Comment

by:Jeffrey Coachman
ID: 39241748
Oh
My
God....
;-)

OK, ...I'll give it a try...
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39241777
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Author Comment

by:Jeffrey Coachman
ID: 39241836
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
 
LVL 74

Author Comment

by:Jeffrey Coachman
ID: 39241858
The new formula does not update when the list is filtered...?
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39241938
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39241943
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
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 39242017
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
 
LVL 74

Author Comment

by:Jeffrey Coachman
ID: 39242289
NB_VC,
Thanks for the info...
0
 
LVL 74

Author Comment

by:Jeffrey Coachman
ID: 39242296
barryhoudini,

NB_VC's first formula seems to still be working, but I will try yours as well, and report back.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39242404
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
 
LVL 74

Author Comment

by:Jeffrey Coachman
ID: 39242409
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39242505
I am okay with that.  Thanks.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39242550
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 0 total points
ID: 39242685
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39243059
does closing request assign points, mods?  Just curious.
0
 
LVL 74

Author Comment

by:Jeffrey Coachman
ID: 39243322
yes
0
 
LVL 74

Author Closing Comment

by:Jeffrey Coachman
ID: 39252603
Thanks!
;-)
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question