RANDBETWEEN function capped

**derek594** · 04-30-2013, 10:19 PM

Hi there,

I have tried to find this answer but no luck as yet.

I am allocating stock to area reps (5600 units). As the stock is different colours I need to allocate it randomly to ensure a good mix of colours to each region.

The next things is I need to cap the offer to each area rep based on another calculation (this is all sorted no help needed).

So essentially what I have is a list of stock (5600 units) and I need to offer it to 6 area reps but cap it.

Eg: #1 would get 350 units
#2 would get 1232 units
and so on up to the 5600 units available.

I know I can use RANDBETWEEN(1,6) but this cannot be capped.

Any help is appreciated.

Thanks Derek

**FDibbins** · 04-30-2013, 10:50 PM

Hi and welcome to the forum

how is the 350, 1232 etc determined?

**derek594** · 04-30-2013, 11:01 PM

hi fdibbins

the numbers are based on their contribution to sales from a previous period.

I am happy to enter them manually into a cell.

Thanks,

**FDibbins** · 04-30-2013, 11:14 PM

what does your data look like? can you upload a sample workbook?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.