Allocate items using a formula

Dear Experts:

I got a worksheet where I need to allocate items to a list of cities.

formula allocation
E2 says that only 68 items can be allocated versus the required 291 items

Can a formula be entered that is capable of showing the partial allocation as seen in E3 or C4

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

I have attached a sample file for your convenience.
Allocation-Excel-Formula-EE.xlsx
Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
Sorry can you clarify your request ? It is not clear what you want ? what should the formula show if you enter 68 ? 36 for Denver ? on what basis and what is the rest ? Kindly explain
gowflow
Andreas HermleTeam leaderAuthor Commented:
Hi gowflow,

thank  you very much for your quick reply. Sorry for the confusion I caused with this question:

To make it simple:

E2 is the cell that receives different number of items to be distributed / allocated

If E2 is less than B10 (The sum of items) a formula or macro should check which city (Top down) will only get a partial allocation and write the maximum of items received next to the city

I hope I could make myself clear :-)

New York = 17 items
Boston = 15 items
Rest = 36 items (Denver will only get 36 items instead of the requested 40 items)
Total = 68 items
gowflowCommented:
ok clear here it is:

This is your formula for the first block put this in C2 and drag it down till the Total of first block in Cell C10
=IF(OR($E$2-SUM($B$2:B2)>0,($E$2-SUM($B$2:B2)+B2)<0),"",($E$2-SUM($B$2:B2)+B2))

This is the formula for the second block put this in Cell C15 and drag it down till the Total in Cell C23
=IF(OR($E$15-SUM($B$15:B15)>0,($E$15-SUM($B$15:B15)+B15)<0),"",($E$15-SUM($B$15:B15)+B15))

Pls chk the attached file.
gowflow
Allocation-Excel-Formula-EE.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Andreas HermleTeam leaderAuthor Commented:
Hi gowflow, I am deeply impressed, wow, what a nice formula job!! ;-)

Thank you very much for your great help. I really appreciate it. Thank you!!
Andreas HermleTeam leaderAuthor Commented:
This is professionalism, I am deeply impressed :-)
gowflowCommented:
Your welcome anytime. Tks for the appreciation.
gowflow
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.