[Solved] Florida not accepting rounding/accounting rounding

[Ferlo]

Got a spreadsheet rounding question.
Florida has moved from a bracket system (rather sadly, really) to this rule: "the computation of the tax must be carried to the third decimal place; if the third decimal place is greater than 4 [i.e., it's 5 or up], the tax must be rounded up to the next cent."

I've been doing this for items sold: =ROUND( (product price cell) *0.06;3), cutting it down to two decimal places to get it to round up on a 5,
and totalling up the tax for the items. It is doing that math correctly. Sales tax of .845 rounds up to .85, and so forth.

But what they really mean (as per an angry letter) is that they'll take no less than (total sales) * .065 as sales tax. So, I've got to be doing something wrong in my rounding, since rounding up to .01 on .005 isn't good enough, right?

[John_Ha]

There is probably a function to ROUNDUP but an easy way to do it is to add 0.5 and then truncate.

3.2 + 0.5 = 3.7 which truncates to 3

3.6 + 0.5 = 4.1 which truncates to 4

As you need three digits, you will need to multiply by 100 first. So

3.234 * 100 = 323.4 + 0.5 = 323.9 which truncates to 323 divided by 100 = 3.23

3.238 * 100 = 323.8 + 0.5 = 324.3 which truncates to 324 divided by 100 = 3.24

Be very aware that the number displayed will not be the actual value - it will be the approximation you have chosen to display to. So, if you divide 2 by 3, the value in the cell is 0.6666666666... If you display 2 digits you will see the number 0.67 but the value will still be 0.666666666... You may want to switch on Value as displayed in Tools > Options > Calc > somewhere ...., so if the cell shows the number 0.67, the value is altered to 0.67(0000000000...).

Calc Functions listed by category

ROUNDUP

Rounds a number up, away from zero, to a certain precision.
Syntax: ROUNDUP(number; places)

returns number rounded up (away from zero) to places decimal places. If places is omitted or zero, the function rounds up to an integer. If places is negative, the function rounds up to the next 10, 100, 1000, etc.

This function rounds away from zero. See ROUNDDOWN and ROUND for alternatives.

Example:

ROUNDUP(1.1111; 2)

returns 1.12

ROUNDUP(1.2345; 1)

returns 1.3

ROUNDUP(45.67; 0)

returns 46

ROUNDUP(-45.67)

returns -46

ROUNDUP(987.65; -2)

returns 1000

If these examples do not seem to work, see Tools - Options - OpenOffice.org Calc - Calculate to check the display precision.

[Ferlo]

Found a solution. Part stupid mistake, part different formula. Thank you!