Truncate or rounding?

[PHnew]

I have a list of financial awards that are the result of a ratio of the whole. The numbers are displayed with 2 decimal points, however when clicking on the cell, you might see 5+ decimal points.

To clear up data that wasn't relevant to the end user, I copied the sheet and pasted values only, then hid columns not suited to the audience.

At the bottom, I calculated a sum to ensure the total balanced with the available funds to be distributed.

Within the list of recipients, there are two categories: living or deceased.

I calculated a separate figure for each of these categories to ensure it balanced with my overall total.

...Then I gave the sheet to the accounting department to write checks. They used the numbers as displayed on my spreadsheet, and typed them into a calculator -- they were .05 cents off -- less than my number.

So....As I approach another like scenario with another client, what is the proper way to set up my spreadsheet so that the numbers are true whether as a result of a formula within the spreadsheet or calculated outside the software?

Truncate comes to mind, but I'm not sure as I don't fully understand those settings.

Thank you for guidance.

 

[shg]

Suppose you had $200 to divide three ways. What checks would your accounting department like to write?

 

[PHnew]

In my situation, I have millions of dollars to spread across hundreds of claimants based on rankings of their circumstance. They are ranked, given an award. but if the award exceeds the money available, they are adjusted proportionately of the hole which results in the final award. The totals on my spreadsheet balance out to the money in the bucket, but when accounting puts the values into the calculator, they come up with a few cents less than what is displayed on my spreadsheet. I can only conclude that it has something to do with rounding because the values, when viewed cell by cell have lengthy decimal points, but only 2 are visible. ???

 

[SpillerBD]

Your spreadsheet is holding onto the values less than a penny. You want Accounting to write checks for those fractions or not? Of course they can't, so how do you want to handle them?
Without iterating with a macro, the only systematic way is with TRUNC. If you use ROUND to 2 decimal places, you would end up with a penny more.

shg asked about $200 three ways for a reason because 3 doesn't go into 200 evenly. $66.67 + 66.67 + 66.66 = 200.00

Typically whenever you deal with money you must calculate to only the penny and round or truncate. (They don't allow coin cutting anymore.)

 

[shg]

If accounting gets upset because they are a nickel shy for a multimillion-dollar distribution, won't they have the same problem if there is a nickel left over?

In the alternative, to go back to the $200 example, shouldn't everyone who is owed a third get the same amount? Would the last party scream because they got a penny less that the others?

There are several ways to solve this, just trying to figure which one makes all the girls happy.

If you round, you will total +/- pennies to correct: =round(yourFormula, 2)

If you truncate, you may have some pennies left over:=trunc(yourFormula,2)

 

[shg]

BTW, is this a rising tide distribution?

 

[PHnew]

Hmmm....thanks, I think. Sounds like I did what I should have and what I'll have to do in the future. No, it's not a rising tide distribution. I guess it's just a game of horseshoes and hand grenades....close, but no cigar.
Thanks all!

 

[shg]

I agree with accounting that the numbers should add up correctly, so you should do one or the other.

The third possibility is to round the result up to the next penny: =roundup(yourFormula, 2)

Then accounting may have to pony up a few cents.