# Help logical Formula

#### santhoshlk

##### Board Regular
hi guyzzz... hopu u can help me

i have listed customers in col-A (150 customer) and col-B got their total collection in past year.

i have 5 sales person with me.

now i need to distribute the customers to each salesperson with following condition

I have 150 customers & 5 Salesmen. so average 30 customers per man
and average of total collection of last year is 20mill.

now each saleman shoul get 30 customers and their last year collection should be around 20 mill.

is it possible

pls help

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
If youv'e only got 150 customers, list them in order of last year's collection. Start with giving the first sales guy the top 15 and bottom 15, and so on.

That probably won't work out neatly for you, but with only 150 customers it should take you about 10 minutes to copy and paste into 5 groups of 30 manually. i don't think you need any formula, other than =average() 5 times.

you are correct I could do that manually....

but actually i 6000+ clients !!!!

pls help me on a formula or with an idea

any news... pls

ia still waiting for a solution guyzzz

hi shg....

thanks for your help... it seems i in closer what io looking for.... can you just explain me how it works... so i can modify as per my need

Cols B and C are sorted in descending value by account value, so the largest accounts are assigned first.

The formula in column D identifies the person with the smallest total in the prior row.

The formulas in cols F:O assign the current account to that person, and maintains a running total. You can insert or delete coluymns interior to this area to increase or decrease the number of people.

Last edited:
I added some conditional formatting so you can visualize the process.

Replies
3
Views
216
Replies
14
Views
1K
Replies
16
Views
541
Replies
9
Views
725
Replies
3
Views
3K

### Forum statistics

1,203,734
Messages
6,057,054
Members
444,902
Latest member
ExerciseInFutility

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back