Money distribution macro

I am trying to find a way to do the following:

Every week when I'm making my payroll I have a certain amount that I am going to distribute amongs various employees. I have a worksheet where I record the excact number of every bill I have for the payroll.

So for example:

12 - 10 dollar bills
24 - 20 dollar bills
40 - 50 dollar bills
8 - 100 dollar bills

This distribution obviouls changes every week according to what bank gives me.

Now I have a (complex) system that tries to distribute these bills "evenly" among my employees, so that the same proportion of bills tries to get handed down. So if someone is getting paid 380 my program will ask me to give them 1 - 100, 5 - 50, 1 - 20, and 1 - 10. However it is not really efficient, and it is prone to error. I have error checking in place, so I know if a quantity is not correct, that way I can fix it. I was wondering if someone knew of some kind of script that could effectively evenly divide all the bills into the amount needed, or if there was no way to do it, to tell me, that way I would know that I need to try and get some bills changed in order to divy up the payroll.

EDIT: For clarity, when I say program, I mean an excel worksheet. I will upload the worksheet I use. I am ok with a formula based solution, or a VBA based solution. The worksheet has an extra zero on each bill denomination, because it is not actually in dollars. The correct denominations are in the worksheet.

What is wrong with your "system" as A2:B6 provides a list of denominations required based on salaries.

The calculation of required denominations could be simplified but the end results would be the same.

Your query could be interpretted as the bank deciding the denominations rather than you requesting them. Another option to consider an individual being given a maximum number of a denomination

e.g. 3,800 could be 2x1000 (rather than 3x1000) + 3x500 ... etc.

Have your employees expressed a preference e.g no denomination > 500?

Yes the problem is the bank isn't always able to provide the denominations I want. So, in fact, what is placed in a2:a6 is what the bank gives me. Then the formulas are supposed to try and find an "appropriate" way to distribute what the bank gives me so that the workers receive similar.

Worker preference varies from worker to worker, but they all mostly need some kind of variation, so they can use small bills for small expenses and larger bills for larger expenses.

From the sample you can see that in this particular case Employees 4 and 5 got incorrect amounts, because the formulas are giving them 3 - 500 and 1 - 200, which is 1700 total, when they should receive 1600. In this particular case what I did was manually notice that employee 7 had 2 - 100 bills, so I gave him an extra 200 and left him with no 100, so that I could give each of 4 and 5 a 100 bill.

These types of errors are common and are honestly easy to fix manually. I was just wondering if there was a systematic way that the formulas could yield a result and if there was always a way to distribute without any errors to do it. And if there was "no possible way" to distribute the bills appropriately generate some kind of message, so that I would have to go back to the bank or to some other bank to change some bills.

Before I used a program, what I would do was basically set the employees payment envelope sorted from highest to lowest, then start with the highest denomination bill with the highest paid employee and would sort through each bill in that denomination until I had no more bills of that denomination left, OR the next bill would surpass a given employee's payment. I would do this for every bill denomination. Seems something a computer could do better.


EDIT: I think I fixed it, but I was getting a strange error. So if you look at the sample, looking at N23 was being passed of as -99.999999998 when I was evaluating formulas, not actually as -100. So part of the formula is -100/200 and rounding that, so there .5 rounds to 1. But -99.999999998 evaluates to .4999 and rounds to 0. If I add a round with zero decimal places to the formula in N23, it evaluates as 100 and the formulas work. However I have no idea why the value was being pushed as 99.999998.

Anyways this isn't really permant fix as I have no idea if it will always work.