Forum Discussion
Round up Problem in excel
Hi,
i have an issue with this spreadsheet in excel. The file version is the latest in Office 365. It was created on a Mac.
The task in this spreadsheed was about to share a correct number of earnings in a mobile video game with other players. So when they play the game and earn points, the spreadsheed was created to calculate the right amount of earnings (digital diamonds) for every player. So if one Player gets about 5.000 points and another about 2.500, the one gets exactely Double of the price.
And as the fact as there were no half diamonds or so, I needed a full number, not a number with floating points.
i created a sheet with a formula (in the attachment), which calculated the full points, than the percentage of each player of these full points and after that the number of digital diamonds or rubins for each player as an earning for their amount of earnings in percentage.
my problem is, that excel rounds up their earnings or full numbers and in sum, the numbers are more than the full earnings. But if I enable one digit in view, the numbers are correct. For the game, it’s not possible to get earnings in floating points.
the calculation of excel is correct in floating points, but not in full points. It should round up or down, but it doesn’t do it on a correct way so the numbers don’t work on the sum.
i Hope I wrote my issue halfway understandable and may be u have s solution for that.
thanks
steffen
3 Replies
In your specific example of counting rubies the rounding error is +1 (71 compared to 70). You should deduct 1 ruby from the player with the highest points, as this will result in the lowest relative adjustment (=fairest). It will also work when your initial rounding would result in 69 rubies.
I've added some formulae to demonstrate this in the attached workbook, hoping that you find it useful.
Note that this solution may not give the correct result in the rare occasion the rounding error is not equal to -1, zero or +1.
Thanks for your Answer and your work. It makes sense in my eyes for this game campaign.
I understand your way, but that's more the philosophical way, with an extra technical Part. What I don't understand is, that MS Excel isn't able to get a correct number of rubies for each Player, because my formulas was right.
I cannot accept, to make for every campaign an extra Field with corrections, just to get this philosophical right. Because may be next round, another player is great, so I have to change the field for this player.
Not sure I follow. Excel is no more than a calculator with a lot of extras. Your formulae may be correct, but your problem relates to rounding the calculated values for each person.
Suppose you want to divide 10 by 3 players, Excel could, as an example, tell you they get 1.333, 3.333 and 5.333 each. But, since your case only accepts whole numbers this becomes 1+3+5=9. There is no way that Excel can automatically determine which of the three shall get the one extra point. My logic says that the one for whom the relative error is least gets the extra one. Nothing philosophical about it.
