Hi Joel,

yes you are right the data is arranged properly, however let me explain this
a little further. the data that i am working with is very large, and there
are multiple ranges such as 0-5K, 5K-10K, and so on, even it goes upto
1M-10M, and each range will have over 100 bucket according to their last
months performance. which could be somewhere between 0.1% of each range to
50%,

and the amount is not the primary key for distribution, however number of
contract is. for example a bucket in 5k-10K is entitled to 3000 contract or
5% of the entire available contracts in the rage 5k-10K according his or her
last months performance, and the total amount in 5k-10K times 5% will result
in 5M, now i must sumbit 3K contracts to this person with the amount as close
as 5M.

Can i or rather you, lol write a macro that extracts 3K contracts out of
100K records with amount as close to 5K, please keep in mind the amount can
come close however the number of contracts must remain the same.

let me know if i have complicated this too much.

"Joel" wrote:

You dont have to start the running total for each bucket. I arranged the
buckets so the ranges get the results you are looking for. The 2nd bucket is
the sum is betsween 200,000 to 300,00. I can write a macro if I knew the
column where the total was located, but I would use the same algorithm that I
explained in my last posting. I wanted to make sure you were hapy with the
algorithm before I suggested a macro. I didn't want to write the macro and
then you didn't like the results.

"David" wrote:

Hi Joel,

I really appreciate your help, however this will give me a running total on
the new column where for example the first 100 records will add up to 100K,
and then i would have to start the same running total to get the next bucket.

Is there away that i can divide the whole 250 records into 4 buckets by
pressing a button or running a macro which would base the division on the
result of dcount and dsum. since i will be doing this in the future with much
larger file and more than 100 buckets

"Joel" wrote:

Your results won't be exact but close to the results you want.

1) Sort the records by amount
2) In a new column on the 1st record that is greater or equal to $500.00 put
this formula
=sum(C$100:C100)

Notice the dollar sign is on the first 100 only where the row number is the
1st row = 500.

3) copy formula down the worksheet.

4) Your buckets can be extract base on the new column amount

bucket 1 : the totals 0 to $100,000
bucket 2 : the totals 100,000 to $200,000
bucket 3 : the totals 200,000 to $500,000
bucket 4 : the totals 500,000 to $1,000,000
bucket 5 : the totals 1,000,000 to $2,000,000


"David" wrote:

Hi everyone,

I have a worksheet with over 20000 records, i did a dcount based on amounts
from 500-5000 and i got a result of 250 records, and dsum based on the same
criteria (500-5000) i got 2,000,000. further i want to distribute the number
of contracts and amount into 4 buckets as follow

Dsum result 10% 10% 30% 50%
total
1,000,000 100,000 100,000 300,000 500,000 1,000,000

Dcount result 10% 10% 30% 50% total
250 25 25 75 125 250

Can i somehow exctract, copy list of contracts in each bucket that will add
up to the dsum amount distribution for each bucket?

for example: a formula or macro that will give me 25 contracts out of 20,000
when i add the amounts it should come up to 100,000 and all contracts must be
between 500-5000.

I really appreciate any help i can get on this one.