Solved

SubTotal with AvaeageIF

Posted on 2013-06-12
18
253 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
  • 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

820 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