Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to put data into different buckets. - PLEASE HELP
I have thousands of rows of data that have one column that I need tohave a
formula that counts how many within three years and puts it into the correct three different buckets accordingly. ex. 0-2 3-5 5-10 and so forth. Columbus Louisville i need it to look through one column and when it matches look at the length of service column and it is between 0-2 it would count as 1 and go to the next columbus match and length of service and so forth. After that I need to do the same thing but instead of counting i need the formula to add up the dollars according to $0-$100, $500-$1000 ect. and sum up the dollars per market. PLEASE HELP ME!!!!!!!!!!!!!!!!!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to put data into different buckets. - PLEASE HELP
Hi,
Try something like this =COUNTIF(Year,"<=2") =SUMPRODUCT(--(Years=3),--(Years<=5) is an example of what you will need for a range or year. This assumes you have a range where you have entered the number of years. (you did not show us any of your data) SUMIF(Dollars,"<=100") SUMPRODUCT(--(Dollars=500),--(Dollars<=1000),Dollars) You left out the range 100 <500? This will sum the dollar data for entries that are <=100 -- If this helps, please click the Yes button Cheers, Shane Devenshire "Mayur" wrote: I have thousands of rows of data that have one column that I need tohave a formula that counts how many within three years and puts it into the correct three different buckets accordingly. ex. 0-2 3-5 5-10 and so forth. Columbus Louisville i need it to look through one column and when it matches look at the length of service column and it is between 0-2 it would count as 1 and go to the next columbus match and length of service and so forth. After that I need to do the same thing but instead of counting i need the formula to add up the dollars according to $0-$100, $500-$1000 ect. and sum up the dollars per market. PLEASE HELP ME!!!!!!!!!!!!!!!!!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to put data into different buckets. - PLEASE HELP
Market TotalAmount Length of Service
Arizona 2,099.97 2 Arizona 35.14 3 Arizona 2,477.96 5 Austin 1,173.81 7 Austin 2,425.14 15 1-3 4-10 11-20 20+ # of Accts % of Total Accts Arizona #DIV/0! Chicago #DIV/0! <$1001 $1001 to $9999 $10000 to $99,999 $100,000 + # of Accts % of Total Accts Total Dollars % of Total $ Columbus 15,833 Chicago 506,144 Okay the top portion is on two tabs. The very first part is about 2000 lines. I am looking for a formula that looks up each market (ex. Arizona) and than the length of service and put it in the buckets (1-3, 4-10, 11-20, etc) I dont want to add the years just want a count in each bucket. Than i need a formula that will look up each market and add the dollars according to each market. I hope that makes sense. This is very complicated. I really appreciate your help. "Shane Devenshire" wrote: Hi, Try something like this =COUNTIF(Year,"<=2") =SUMPRODUCT(--(Years=3),--(Years<=5) is an example of what you will need for a range or year. This assumes you have a range where you have entered the number of years. (you did not show us any of your data) SUMIF(Dollars,"<=100") SUMPRODUCT(--(Dollars=500),--(Dollars<=1000),Dollars) You left out the range 100 <500? This will sum the dollar data for entries that are <=100 -- If this helps, please click the Yes button Cheers, Shane Devenshire "Mayur" wrote: I have thousands of rows of data that have one column that I need tohave a formula that counts how many within three years and puts it into the correct three different buckets accordingly. ex. 0-2 3-5 5-10 and so forth. Columbus Louisville i need it to look through one column and when it matches look at the length of service column and it is between 0-2 it would count as 1 and go to the next columbus match and length of service and so forth. After that I need to do the same thing but instead of counting i need the formula to add up the dollars according to $0-$100, $500-$1000 ect. and sum up the dollars per market. PLEASE HELP ME!!!!!!!!!!!!!!!!!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to put data into different buckets. - PLEASE HELP
.. This is very complicated
Not really. You can frame it all up (the multi-criteria counts and sums) quite easily using sumproduct. Just a matter of getting familiar with it. Here's a working sample based on your data which shows how to frame up all of the bucketing summaries that you have in mind: http://freefilehosting.net/download/44fme Bucketing summary.xls Contents are 5 sheets comprising: x = source data sheet (your sample data in cols A to C) Counts by Length of Service buckets Sums (of TotalAmount) by Length of Service buckets Counts (of TotalAmount) by TotalAmount buckets Sums (of TotalAmount) by TotalAmount buckets An example or 2 of framing it up given below For bucket: 1-3 (Length of Service) -------------------------------------- Counts by Length of Service: =SUMPRODUCT((x!$A$2:$A$6=$A3)*(x!$C$2:$C$61)*(x!$ C$2:$C$6<=3)) Sums (of TotalAmount) by Length of Service =SUMPRODUCT((x!$A$2:$A$6=$A3)*(x!$C$2:$C$61)*(x!$ C$2:$C$6<=3),x!$B$2:$B$6) For bucket: <$1001 (TotalAmount) --------------------------------------- Counts (of TotalAmount) by TotalAmount buckets =SUMPRODUCT((x!$A$2:$A$6=$A3)*(x!$B$2:$B$6<1001)) Sums (of TotalAmount) by TotalAmount buckets: =SUMPRODUCT((x!$A$2:$A$6=$A3)*(x!$B$2:$B$6<1001),x !$B$2:$B$6) P/s: Ensure that there are no gaps in the various buckets when you frame it up (adapt the operators, eg: =, <, etc accordingly to suit the lower/upper limits for each bucket). Adapt the range to suit your actual source data extents. If the above helps, press the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Mayur" wrote: Market TotalAmount Length of Service Arizona 2,099.97 2 Arizona 35.14 3 Arizona 2,477.96 5 Austin 1,173.81 7 Austin 2,425.14 15 1-3 4-10 11-20 20+ # of Accts % of Total Accts Arizona #DIV/0! Chicago #DIV/0! <$1001 $1001 to $9999 $10000 to $99,999 $100,000 + # of Accts % of Total Accts Total Dollars % of Total $ Columbus 15,833 Chicago 506,144 Okay the top portion is on two tabs. The very first part is about 2000 lines. I am looking for a formula that looks up each market (ex. Arizona) and than the length of service and put it in the buckets (1-3, 4-10, 11-20, etc) I dont want to add the years just want a count in each bucket. Than i need a formula that will look up each market and add the dollars according to each market. I hope that makes sense. This is very complicated. I really appreciate your help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to put data into different buckets. - PLEASE HELP
It didn't work because it doesn't lookup through the 2000 rows to find the
market. I get a value# error. Do you want to see my file? Thank you so much for helping me. "Max" wrote: .. This is very complicated Not really. You can frame it all up (the multi-criteria counts and sums) quite easily using sumproduct. Just a matter of getting familiar with it. Here's a working sample based on your data which shows how to frame up all of the bucketing summaries that you have in mind: http://freefilehosting.net/download/44fme Bucketing summary.xls Contents are 5 sheets comprising: x = source data sheet (your sample data in cols A to C) Counts by Length of Service buckets Sums (of TotalAmount) by Length of Service buckets Counts (of TotalAmount) by TotalAmount buckets Sums (of TotalAmount) by TotalAmount buckets An example or 2 of framing it up given below For bucket: 1-3 (Length of Service) -------------------------------------- Counts by Length of Service: =SUMPRODUCT((x!$A$2:$A$6=$A3)*(x!$C$2:$C$61)*(x!$ C$2:$C$6<=3)) Sums (of TotalAmount) by Length of Service =SUMPRODUCT((x!$A$2:$A$6=$A3)*(x!$C$2:$C$61)*(x!$ C$2:$C$6<=3),x!$B$2:$B$6) For bucket: <$1001 (TotalAmount) --------------------------------------- Counts (of TotalAmount) by TotalAmount buckets =SUMPRODUCT((x!$A$2:$A$6=$A3)*(x!$B$2:$B$6<1001)) Sums (of TotalAmount) by TotalAmount buckets: =SUMPRODUCT((x!$A$2:$A$6=$A3)*(x!$B$2:$B$6<1001),x !$B$2:$B$6) P/s: Ensure that there are no gaps in the various buckets when you frame it up (adapt the operators, eg: =, <, etc accordingly to suit the lower/upper limits for each bucket). Adapt the range to suit your actual source data extents. If the above helps, press the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Mayur" wrote: Market TotalAmount Length of Service Arizona 2,099.97 2 Arizona 35.14 3 Arizona 2,477.96 5 Austin 1,173.81 7 Austin 2,425.14 15 1-3 4-10 11-20 20+ # of Accts % of Total Accts Arizona #DIV/0! Chicago #DIV/0! <$1001 $1001 to $9999 $10000 to $99,999 $100,000 + # of Accts % of Total Accts Total Dollars % of Total $ Columbus 15,833 Chicago 506,144 Okay the top portion is on two tabs. The very first part is about 2000 lines. I am looking for a formula that looks up each market (ex. Arizona) and than the length of service and put it in the buckets (1-3, 4-10, 11-20, etc) I dont want to add the years just want a count in each bucket. Than i need a formula that will look up each market and add the dollars according to each market. I hope that makes sense. This is very complicated. I really appreciate your help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to put data into different buckets. - PLEASE HELP
Mayur,
In adapting to suit, you must of course, extend the rows range in the formulas to cover your actuals. And change the cols and sheetnames accordingly to suit as well. Otherwise nothing will work. I've sent over the solution adapted to suit in private email Please keep all discussions within the newsgroup And do not send any file unless it is requested for Pl mark all responses which help answer your queries by pressing the YES buttons (like the ones below) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Mayur" wrote: It didn't work because it doesn't lookup through the 2000 rows to find the market. I get a value# error. Do you want to see my file? Thank you so much for helping me. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning Dollar Values to 'Buckets' | Excel Discussion (Misc queries) | |||
formula involving buckets | Excel Worksheet Functions | |||
Moving data into different buckets (tax preparation) | Excel Discussion (Misc queries) | |||
how do i put dates into workweek buckets in excel | Excel Discussion (Misc queries) | |||
Data Buckets (in a range) | Excel Discussion (Misc queries) |