Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Totaling pre-defined groups?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Totaling pre-defined groups?
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(D1E1,"",SUMIF(A2:A15,"="&D1,B2:B15)-SUMIF(A2:A15,""&E1,B2:B15)) Biff "nummel" <u27031@uwe wrote in message news:66bb335fe7d68@uwe... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Totaling pre-defined groups?
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/assistan...lconPT101.aspx nummel wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Totaling pre-defined groups?
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(D1E1,"",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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Totaling pre-defined groups?
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: 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/assistan...lconPT101.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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Totaling pre-defined groups?
You're welcome. Thanks for the feedback!
Biff "nummel via OfficeKB.com" <u27031@uwe wrote in message news:66c482933693e@uwe... 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(D1E1,"",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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I an option button in two groups? | Excel Worksheet Functions | |||
defined cell as currency but still have to enter decimal point | Excel Worksheet Functions | |||
Using VLOOKUP with abitlity to choose from multiple defined names. | Excel Worksheet Functions | |||
Dealing with worksheet groups | Excel Worksheet Functions | |||
Default User Defined Functions - How? | Excel Discussion (Misc queries) |