need formula help

[Carmz]

Hello,

I am making a workbook to count tips I need to distribute to employees. I
am looking for the formula that will take into consideration the total number
of bills I have of each denomination and then return to me what amount of
each kind of bill I need to give each person to get them their total amount
of tips for the week. Is it a distribution function?

Thank you!

 

[Joel]

This is a bit complicated. This is a mathematical problem Called Packing
Problem that the Greeks tried to solve. You have a very simple Packing
Problem. Lets look at a very simple case.

There are two employees. the amount of tips is

QNTY Type of bill
1 - Ten Dollar bill
2 - Five Dollar bills
1 - One dollar bill

You first need to divide the total amount by the number of employees. So
you have $21.00 / 2 = $ 10.50 . Algorithms like this usally start by looking
a the largest item first.

You start by calculating how many Ten Dollar Bills you need to to get
$10.50. then you subtract $10.50 - $ 10.00 and get $.50. No more division
is needed.

Next you subtract the number of bill given to the first person beofre you
calculate the 2nd persons part of the tip

QNTY Type of bill
0 - Ten Dollar bill
2 - Five Dollar bills
1 - One dollar bill

Now you calculate the 2nd person tip. The second person get no Ten dollar
bills instead will get 2 five dollar bills.

You can set this up with formulas on the worksheet. The formulas are a
little complicated but can be done. You can also do this with a UDF macro.

 

[Carmz]

Thank you Joel! I will look up both of your suggestions and see what I can do.

Thanks again

 

[Radiolistener]

Hi Carmz,

I thought this was an excellent question. I have created a video solution
on my web site: http://radiolistenersElectricEasel.com There you will find
a video explaining how to create the solution and the spreadsheet with the
solution.

I hope you like it!

Thanks,

John.

 

[Carmz]

thanks bud! I will check it out!

Hi Carmz,

I thought this was an excellent question. I have created a video solution
on my web site: http://radiolistenersElectricEasel.com There you will find
a video explaining how to create the solution and the spreadsheet with the
solution.

I hope you like it!

Thanks,

John.