Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need formula help
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
need formula help
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" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
need formula help
Thank you Joel! I will look up both of your suggestions and see what I can do.
Thanks again :) "Joel" wrote: 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" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
need formula help
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" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
need formula help
thanks bud! I will check it out!
"Radiolistener" wrote: 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" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|