Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SubTotal with AvaeageIF

Posted on 2013-06-12
18
Medium Priority
?
280 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 1000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 1000 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

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!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

783 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