Totaling pre-defined groups? "nummel" <u27031[ at ]uwe> 23.09.2006 17:52:11 I am not an excel wiz and I have a problem that is hard for me to solve. I would like to total the # of units for a defined height range. For example: how many units are within the height range of 38-44? or 46-51? I would like to have a simple macro that references cell addresses for the min and max height ranges I wish to total. Below is data examples. The auto filter works ok, but I want have something that is not so labor intensive to re-use over and over. I have lots of data and want to try many different combinations. Thanks in advance... height # units 38 193 39 318 40 716 41 961 42 997 43 1086 44 2169 45 1867 46 1413 47 1158 48 1017 49 876 50 733 51 401

Re: Totaling pre-defined groups? "Biff" <biffinpitt[ at ]comcast.net> 23.09.2006 18:18:40 Try this: Height in column A, A2:A15 Units in column B, B2:B15 Enter your range variables in a couple of cells, say, D1 and E1: D1 = 38 E1 = 44 =IF(D1>E1,"",SUMIF(A2:A15,">="&D1,B2:B15)-SUMIF(A2:A15,">"&E1,B2:B15)) Biff "nummel" <u27031[ at ]uwe> wrote in message news:66bb335fe7d68[ at ]uwe... [Quoted Text] >I am not an excel wiz and I have a problem that is hard for me to solve. I > would like to total the # of units for a defined height range. For > example: > how many units are within the height range of 38-44? or 46-51? I would > like > to have a simple macro that references cell addresses for the min and max > height ranges I wish to total. Below is data examples. The auto filter > works ok, but I want have something that is not so labor intensive to > re-use > over and over. I have lots of data and want to try many different > combinations. Thanks in advance... > > height # units > 38 193 > 39 318 > 40 716 > 41 961 > 42 997 > 43 1086 > 44 2169 > 45 1867 > 46 1413 > 47 1158 > 48 1017 > 49 876 > 50 733 > 51 401 >

Re: Totaling pre-defined groups? Dave Peterson <petersod[ at ]verizonXSPAM.net> 23.09.2006 18:28:27 I would use another column that groups my Heights. I'm not quite sure how to determine the categories, though. If you had said 38-44, 45-51, 52-58, ... (each group spanning 7 whole numbers), you could use: =INT((A1-3)/7)*7+3 Then 38-44 would be classified as 38. 45-51 would be 45 52-58 would be 52 ..... Then you could filter on that column. ======= Another option would be to apply Data|pivottable. You can group that data by a standard interval and see things like: Sum of qty Number Total 31-37 15 38-44 12 45-51 18 52-58 23 59-65 13 66-72 18 73-79 17 80-86 17 87-93 17 94-100 20 101-107 17 Add headers to row 1 (if you don't have them) select the range A1:Bxxx Data|pivottable Follow the wizard until you get to a dialog with a Layout button on it. Click that layout button drag the header for the height to the Row field drag the header for the units to the data field And finish up the wizard. Then rightclick on the Height button and choose Group and Show Detail, then Group. Then starting at 24 (say) ending at 197 (or whatever you want) by: 7 and watch the results. (You could also do the categorization yourself and then use that in your pivottable. If you want to read more about pivottables... Here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx nummel wrote: [Quoted Text] > > I am not an excel wiz and I have a problem that is hard for me to solve. I > would like to total the # of units for a defined height range. For example: > how many units are within the height range of 38-44? or 46-51? I would like > to have a simple macro that references cell addresses for the min and max > height ranges I wish to total. Below is data examples. The auto filter > works ok, but I want have something that is not so labor intensive to re-use > over and over. I have lots of data and want to try many different > combinations. Thanks in advance... > > height # units > 38 193 > 39 318 > 40 716 > 41 961 > 42 997 > 43 1086 > 44 2169 > 45 1867 > 46 1413 > 47 1158 > 48 1017 > 49 876 > 50 733 > 51 401 -- Dave Peterson

Re: Totaling pre-defined groups? "nummel via OfficeKB.com" <u27031[ at ]uwe> 24.09.2006 11:38:27 Thanks Biff, this will work great for my application, I appreciate the help! Biff wrote: [Quoted Text] >Try this: > >Height in column A, A2:A15 >Units in column B, B2:B15 > >Enter your range variables in a couple of cells, say, D1 and E1: > >D1 = 38 >E1 = 44 > >=IF(D1>E1,"",SUMIF(A2:A15,">="&D1,B2:B15)-SUMIF(A2:A15,">"&E1,B2:B15)) > >Biff > >>I am not an excel wiz and I have a problem that is hard for me to solve. I >> would like to total the # of units for a defined height range. For >[quoted text clipped - 23 lines] >> 50 733 >> 51 401 -- Message posted via http://www.officekb.com

Re: Totaling pre-defined groups? "nummel via OfficeKB.com" <u27031[ at ]uwe> 24.09.2006 11:43:38 Thanks for the information. I had tried the grouping with the pivot table, which worked fine as long as your grouping was uniform. The links were full of good information also. thanks again for your help... Dave Peterson wrote: [Quoted Text] >I would use another column that groups my Heights. > >I'm not quite sure how to determine the categories, though. > >If you had said 38-44, 45-51, 52-58, ... (each group spanning 7 whole numbers), >you could use: > >=INT((A1-3)/7)*7+3 > >Then 38-44 would be classified as 38. > 45-51 would be 45 > 52-58 would be 52 >.... > >Then you could filter on that column. > >======= >Another option would be to apply Data|pivottable. > >You can group that data by a standard interval and see things like: > >Sum of qty >Number Total >31-37 15 >38-44 12 >45-51 18 >52-58 23 >59-65 13 >66-72 18 >73-79 17 >80-86 17 >87-93 17 >94-100 20 >101-107 17 > >Add headers to row 1 (if you don't have them) >select the range A1:Bxxx >Data|pivottable >Follow the wizard until you get to a dialog with a Layout button on it. >Click that layout button >drag the header for the height to the Row field >drag the header for the units to the data field > >And finish up the wizard. > >Then rightclick on the Height button and choose Group and Show Detail, then >Group. >Then starting at 24 (say) >ending at 197 (or whatever you want) >by: 7 > >and watch the results. > >(You could also do the categorization yourself and then use that in your >pivottable. > >If you want to read more about pivottables... > >Here are a few links: > >Debra Dalgleish's pictures at Jon Peltier's site: >http://peltiertech.com/Excel/Pivots/pivottables.htm >And Debra's own site: >http://www.contextures.com/xlPivot01.html > >John Walkenbach also has some at: >http://j-walk.com/ss/excel/files/general.htm >(look for Tony Gwynn's Hit Database) > >Chip Pearson keeps Harald Staff's notes at: >http://www.cpearson.com/excel/pivots.htm > >MS has some at (xl2000 and xl2002): >http://office.microsoft.com/downloads/2000/XCrtPiv.aspx >http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx > >> I am not an excel wiz and I have a problem that is hard for me to solve. I >> would like to total the # of units for a defined height range. For example: >[quoted text clipped - 20 lines] >> 50 733 >> 51 401 > -- Message posted via http://www.officekb.com

Re: Totaling pre-defined groups? "Biff" <biffinpitt[ at ]comcast.net> 24.09.2006 19:00:32 You're welcome. Thanks for the feedback! Biff "nummel via OfficeKB.com" <u27031[ at ]uwe> wrote in message news:66c482933693e[ at ]uwe... [Quoted Text] > Thanks Biff, this will work great for my application, I appreciate the > help! > > Biff wrote: >>Try this: >> >>Height in column A, A2:A15 >>Units in column B, B2:B15 >> >>Enter your range variables in a couple of cells, say, D1 and E1: >> >>D1 = 38 >>E1 = 44 >> >>=IF(D1>E1,"",SUMIF(A2:A15,">="&D1,B2:B15)-SUMIF(A2:A15,">"&E1,B2:B15)) >> >>Biff >> >>>I am not an excel wiz and I have a problem that is hard for me to solve. >>>I >>> would like to total the # of units for a defined height range. For >>[quoted text clipped - 23 lines] >>> 50 733 >>> 51 401 > > -- > Message posted via http://www.officekb.com >