Solved

SubTotal with AvaeageIF

Posted on 2013-06-12
18
249 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now