 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / September 2006Filters and totals
I have a sheet with data that includes totals in the bottom row. I would like to be able to invoke automatic filtering such that the totals change dynamically to cover only the visible rows. I can't figure out any reasonably easy ways to do this. Folks? A second question: how can I create filters that include my Totals row without having to go in manually each time and change something? E.g., I go to Custom in the filter menu and select the main filter selection, then add "or Does not contain ?", and that plops my Totals row in, since it doesn't have any value supplied in the column I'm filtering, but all other rows do have a value in that column. But that's too much work. Dallman Have a look at the subtotal function =subtotal(9,A2:A100) The 9 is to add There are other numbers to get average , min, max etc. Greetings from NZ >I have a sheet with data that includes totals in the bottom row. > I would like to be able to invoke automatic filtering such that [quoted text clipped - 10 lines] > > Dallman > Have a look at the subtotal function > > =subtotal(9,A2:A100) Thank you. That works just as I'd hoped for the columns where I need a defined function such as sum, average, etc. Cool beans. However, how can I do a SUMPRODUCT on just the rows that are showing in my filter? E.g., a weighted average using data from two columns. ----------------- > >I have a sheet with data that includes totals in the bottom row. > > I would like to be able to invoke automatic filtering such that [quoted text clipped - 10 lines] > > > > Dallman You could multiply the two columns and take an average of the result using the subtotal function..? Bill K >> Have a look at the subtotal function >> [quoted text clipped - 22 lines] >> > >> > Dallman > You could multiply the two columns and take an average of the > result using the subtotal function..? Okay, but I don't really want extra data sitting there that I don't want to be looking at (the multiplication totals). I do lots of sorting and stuff on these columns with macros, too, and extra or hidden rows will mess me up. I'd have to have a spare column for this that I don't have right now. Yes, I could hide it, but it's an ugly kludge and causes me to have to revise all sorts of macros, etc. I suppose I could put it in a hidden worksheet, but even that is not my idea of a clean solution. Any other ideas? Or any refinements that will let me keep the multiplication subtotals without having to eyeball them constantly? ---------------- > >> Have a look at the subtotal function > >> [quoted text clipped - 26 lines] > >> > > >> > Dallman Hi Could you not carry out the Sumproduct based upon the two columns of data and the same criteria used for your Filter, then divide that answer by the result of Subtotal(9,data) With Names in Column J, Count in Column K and Value in column L, if the Filter was applied for Name "A" in column J then =SUMPRODUCT((J3:J100)*(K3:K100)*(I3:I100="A"))/SUBTOTAL(9,J3:J100) will return the result you are seeking.  SignatureRegards Roger Govier >> You could multiply the two columns and take an average of the >> result using the subtotal function..? [quoted text clipped - 46 lines] >> >> > >> >> > Dallman > Could you not carry out the Sumproduct based upon the two columns > of data and the same criteria used for your Filter, then divide [quoted text clipped - 4 lines] > =SUMPRODUCT((J3:J100)*(K3:K100)*(I3:I100="A"))/SUBTOTAL(9,J3:J100) > will return the result you are seeking. That would work, yes, and I thought of that first thing. But I don't see how to have the formula know what "A" is. I want to change the filtered data on the fly and still have the weighted average come out right. If I have data like so -- I'm using Column "A" for filtering, btw -- A B C D ... ... 22 EEK 23 FOO 24 FOO 25 FOO 26 BAR 27 BAR 28 BAZ ... 101 Totals ... I can filter for FOO. How does the formula know that I3:I100="FOO"? I can't use OFFSET from 101, because I don't know how far away in rows the last FOO is. Also, I might filter on more than one name at a time. Dallman Hi Dallman Then try the following =SUMPRODUCT((J4:J100)*(K4:K100) *(SUBTOTAL(3,OFFSET($J$4,ROW($J$4:$J$100)-ROW($J$4),,1)))) /SUBTOTAL(9,J4:J100) This is an adaptation from a posting by the Subtotal "maestro" (Bob Phillips) made in Dec 2005 http://snipurl.com/x4qw SignatureRegards Roger Govier >> Could you not carry out the Sumproduct based upon the two columns >> of data and the same criteria used for your Filter, then divide [quoted text clipped - 30 lines] > > Dallman Hi >from a posting by the Subtotal "maestro" meant to say, the SUMPRODUCT "maestro" SignatureRegards Roger Govier > Hi Dallman > [quoted text clipped - 41 lines] >> >> Dallman > Then try the following > =SUMPRODUCT((J4:J100)*(K4:K100) [quoted text clipped - 4 lines] > Phillips) made in Dec 2005 > http://snipurl.com/x4qw Roger, Interesting, and a head-scratcher. Despite trying at length, I can't get it to work. :-( But also, I don't see how it could, because there seems to be a column missing. Here is some actual sample data. Note that Column A contains the string I'm filtering on; Column N contains a whole number comprising days held; and Column G contains the dollar cost. I want to average the days held as weighted by the cost. The data starts in Row 2. I've left out some columns that are not relevant here (but kept others for an overall picture). Numbers at far left are actual row numbers for the filtered information in this example. A G K L N O P ------------ ---------- -------------------- ----------------------- Days Running Nominal 1 Descrip. Cost Proceeds G/L Held Retn Retn ------------ ---------- -------------------- ----------------------- 473 SOHU.COM INC 5,006.00 5,347.83 341.83 4 6.83% 6.83% 474 SOHU.COM INC 5,006.00 5,047.84 41.84 28 3.83% 0.84% 475 SOHU.COM INC 482.80 504.78 21.98 7 3.87% 4.55% 476 SOHU.COM INC 4,345.20 4,712.25 367.05 11 5.21% 8.45% 477 SOHU.COM INC 241.40 261.79 20.39 11 5.26% 8.45% 478 SOHU.COM INC 465.20 523.59 58.39 10 5.48% 12.55% 479 SOHU.COM INC 4,652.00 4,525.86 (126.14) 65 3.59% -2.71% 480 SOHU.COM INC 965.60 905.17 (60.43) 41 3.14% -6.26% ------------ ---------- -------------------- ----------------------- 620 Totals 21,164.20 21,829.11 664.91 59.55 3.14% The wrong value "59.55" in the Totals row for Days Held is actually the value for all my data. The value that ought to appear here as a "subtotal" is 26.49 days. Roger, I figured it out. I saw a message here by Ron Rosenfeld, Message-ID: <pdagh2hpnqu1af22pe64e0748s0qkr7rc7@4ax.com>, suggesting: Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr Well, I did. The formula that works for me now is this: =SUMPRODUCT(ARRAY.FILTER(N1:N619),ARRAY.FILTER(G1:G619))/SUBTOTAL(9,G1:G619) Woo-hoo! In any case, I found your help refreshing and worth cogitating over. :-) So thank you. Dallman Hi Dallman Glad you managed to get a result using Laurent Longre's very powerful addin. But, just out of interest, I also get a result of 26.49 with your data if I use =SUMPRODUCT((N2:N619)*(G2:G619) *(SUBTOTAL(3,OFFSET($G$2,ROW($G$2:$G$619)-ROW($G$2),,1)))) /SUBTOTAL(9,G2:G619) >I don't see how it could .. (work) ..because there seems to be a column >missing. The formula is using the 2 columns G and N just like your solution. The part SUBTOTAL(3,OFFSET($G$2,ROW($G$2:$G$619)-ROW($G$2),,1))) is returning an array of 0's for the filtered rows, and 1's for the visible rows which therefore only gives the product of G and N for the visible rows. Maybe you used SUBTOTAL(9,N2:N619) as your divisor, which would have given you the weighted Cost of 3167.49 rather than the weighted days. SignatureRegards Roger Govier > Roger, > [quoted text clipped - 12 lines] > > Dallman > I also get a result of 26.49 with your data > if I use > > =SUMPRODUCT((N2:N619)*(G2:G619) > *(SUBTOTAL(3,OFFSET($G$2,ROW($G$2:$G$619)-ROW($G$2),,1)))) > /SUBTOTAL(9,G2:G619) > The part > SUBTOTAL(3,OFFSET($G$2,ROW($G$2:$G$619)-ROW($G$2),,1))) [quoted text clipped - 6 lines] > as your divisor, which would have given you the weighted Cost of 3167.49 > rather than the weighted days. Bingo! Good intuition. All I can say it, it was 2:30 in the morning. :-) I'm still having trouble seeing what the OFFSET stuff is about in the part you further elucidated. Maybe you can help me there, too. I do see that the "width" value (last '1' in the OFFSET statement) is not required in my case, as the default is the same as the width of the reference, also 1. I'm also wondering if I should put $ anchors on the array delimiters that don't have them. It seems odd that some do and some don't. Thanks again! Dallman Hi Dallma As you say the width element isn't necessary in this particular case, so the final 1 isn't strictly necessary. What the Offset returns is an array of numbers that occurs in a single column between G2 and G619. If you try in on a much smaller subset, and highlight that part of the formula in the formula bar and press F9, you will see the array. Wrapping that in Subtotal(3,array) carries out a CountA function on the whole array, but as Subtotal only deals with visible rows, it will give a positive count for any visible row that has a value (1) and a 0 for any row that is either hidden, or has no value. So you have for example 5006*4*1 for row 473, whereas you could have had 5006 *4 * 0 fro row 472 (as it is hidden - I don't know what the actual values were, but it is not important, as the final 0 makes the value zero anyway.) As far as absolute and relative references are concerned, as the formula is not being dragged across to any other cells, then it makes no difference in this particular case. I guess I did it out of habit in part of the formula, but was lazy in the rest. Normally I would have made them all absolute. SignatureRegards Roger Govier >> I also get a result of 26.49 with your data >> if I use [quoted text clipped - 35 lines] > > Dallman > What the Offset returns is an array of numbers that occurs in > a single column between G2 and G619. If you try in on a much [quoted text clipped - 9 lines] > what the actual values were, but it is not important, as the > final 0 makes the value zero anyway.) Yes, thank you, Roger! I understand the basic principle. I was (and still am) having some trouble visualizing the functionality of the OFFSET function itself in the larger formula. I do use OFFSET frequently in my own formulas, so I know in general how it works; but this advanced use of it was beyond me. When I tried manually calculating the values in individual cells, it seemed always to be zero. (Maybe I did something wrong.) When I tried removing the OFFSET and just letting the SUBTOTAL(3,...) work alone, it didn't work. So I'm a bit confused. Oh, well, it's a slick, if very complex, approach to a problem, and I am very grateful to have learned of it. I have a couple of other questions that I will post seperately when I am able to work through them. Thanks again! Dallman |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.