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]

Hi,

What is your logic that the sum should be 1080 rather than 1062?
If the results of your numbers after the rounding is correct, then, the sum is 1062.
If the sum should be 1080, then, that means the problem is with the rounding, so which of the numbers are not rounded the way you want?

 

[shg]

One of many ways:

	A​	B​	C​
1​	Input​	Sort​	​
2​	24.13408​	18​	B2: {=IF(SUM(CEILING(A2:A$12, 9), B$1:B1) > 1080, FLOOR(A2, 9), CEILING(A2, 9))}
3​	108.60340​	108​
4​	144.80450​	144​
5​	30.16760​	27​
6​	108.60340​	108​
7​	156.87150​	153​
8​	156.87150​	153​
9​	108.60340​	108​
10​	120.67040​	126​
11​	108.60340​	117​
12​	12.06704​	18​

This just rounds up as many as necessary at the bottom of the list.

 

[robfl22]

shg....thank you. Do I copy that formula all the way down?

 

[shg]

Yup.

 

[robfl22]

Hi,

What is your logic that the sum should be 1080 rather than 1062?
If the results of your numbers after the rounding is correct, then, the sum is 1062.
If the sum should be 1080, then, that means the problem is with the rounding, so which of the numbers are not rounded the way you want?

I am responsible for the distribution of tips to employees on a nightly basis depending on how many hours each employee worked. Every night, there are some tips, we call it funny money, which comes from a customers credit card. Each funny money bill is worth 9 dollars in actual cash. I have to distribute these funny bills based on the hours worked. In this case, I had 120 funny bills which total 1080 in actual cash. What I did was take the 1080 and divide that by the total hours worked by all employees to come up with an hourly rate. Then I multiplied the hourly rate by each employees hours worked. Then I used the round function. Ultimately, I obviously want to automate this on a nightly basis in excel.

 

[shg]

In that case, you want to so something a little more fair, like minimizing the rounding error.

 

[jtakw]

Yeah, I think employee 3, 6, 9 is not going like employee 11 very much, same goes for 2 and 12.

 

[robfl22]

I am more than open to better suggestion believe me. We also have actual cash tips as well. Essentially what I do is create two hourly rates. One for the funny money bills and the other hourly rate for the actual cash. Then I add those two hourly rates together to get one hourly rate. But again, my problem is the distribution of these funny money bills. I spend literally an hour just on the allocation of these tips on a nightly basis and it sucks. I want to automate it so I can get it done in minutes. There has to be an easy way....just not sure how to go about it.

 

[robfl22]

How about this example: I have 10 people pooling tips but not all 10 work each night. On this particular night, Pat worked 2 hours, Bill worked 10 hours, Kirk, Greg, and Dan worked 8 hours, Bob worked 6 hours and Lisa worked 5 hours. There are 72 funny money bills valued at 9 dollars each for a total of 648 dollars. The actual cash tips we have is 850 dollars. So the total tip pool has 648+850 which equal 1498 dollars. The total amount of hours worked were 47 hours. Knowing that the funny bills need to be distributed fairly equally, how would you go about it? I seriously seriously appreciate your help if I could automate this.