Round function

[robfl22]

Hi guys, hope you can help me. I have a series of numbers where I am rounding that number by the nearest 9. The round function works nicely however when I add these numbers up, it does not equal the total I am seeking. Here are the numbers:
24.13408 Round Number 27
108.6034 Round Number 108
144.8045 Round Number 144
30.1676 Round Number 27
108.6034 Round Number 108
156.8715 Round Number 153
156.8715 Round Number 153
108.6034 Round Number 108
120.6704 Round Number 117
108.6034 Round Number 108
12.06704 Round Number 9

Now when I add the round numbers, it totals 1062. I need to this add up to 1080. The difference is 18 so I essentially have 2 9s to be distributed so that it equals 1080. Is there a way to do this? Thanks in advance!

 

[jtakw]

So the funny money is as good as real money?

If it is, why not just combine them before the calculations?

May be something like this to build on?

Excel 2010
	A	B	C	D	E	F
1	Name	Hours Worked	Tips Earned		Funny Bills	Cash Tips
2	Pat	2	64		72	850
3	Bill	10	319		Cash equivalent	648
4	Kirk	8	255
5	Greg	8	255		Total Tips Received	1498
6	Dan	8	255
7	Bob	6	191
8	Lisa	5	159
9	Joe	0	0
10	Jack	0	0
11	Jane	0	0
12
13	Total Hours	47
14	Total Tips Distributed		1498
Sheet2

Cell Formulas
Range	Formula
B13	=SUM(B2:B11)
C2	=ROUND(F$5/B$13*B2,0)
C14	=SUM(C2:C11)
F3	=E2*9
F5	=F2+F3

 

[robfl22]

I wish it were that easy. Yes the funny money acts as real money. However, I have to treat the funny money separate because of tax purposes. I could easily just hand out the all of the funny money to a few individuals, but I have to be fair to everyone.

 

[jtakw]

Ok, for tax purposes, for the employee, is it better to receive real money or funny money?

 

[robfl22]

Real money. Another problem I run into is on the off nights, sometimes the funny money tips will be a very small amount if not zero. So obviously there may be one or two people that end up with a funny money bill as part of their total cut. I try to be fair about that by going back thru previous nights to see who got the funny money bills and who didnt.

 

[jtakw]

I'm still trying to come up with something to help, but it's getting late here, and I need to take care of other things right now.
Do you have a spread sheet that you're currently using? If you do, it will probably help if you can show a sample, so I or others can see what your current set up is.
If not, don't worry about it.

 

[robfl22]

I totally understand and I really appreciate you taking the time to work with me on this. I have somewhat of a spreadsheet but to be honest it is a mess because I have been trying different things to get it to work on all levels of tips. So, I highly doubt it would be helpful. Thanks again jtakw.

 

[jtakw]

You're welcome, will see if I can come up with something, or may be other members may have some ideas.

 

[shg]

Putting aside the funny-money for a moment, there's a workbook to handle tip-outs at https://app.box.com/s/7eure8q5bljsn9nkjgx2

 

[robfl22]

Thanks shg. The actual cash part of it is easy. I get that part of it. Question: do you think I am on the right track by doing two separate hourly rates, the funny money and actual cash? Or is there some type of distribution formula for the amount of funny money bills disregarding the amount they are worth. For instance if I have 50 funny money bills, is there a potential formula to distribute them out based on hours worked?

 

[shg]

I'm sure there is, but at the moment, I don't understand any difference between funny money and cash that would cause you to treat them differently.

If it's less value than real money, then perhaps you could quantify that.

One solution would be to just keep it, and distribute some lesser amount of real money to the employees instead.