Functions: Round, Int, Fix, Floor Ceiling across Excel, MSAccess, MSSQL TSQL

[Rx_]

My data moves from Access prototypes to SQL Server (using scalar functions), and outputs into Excel using VBA with Excel Object Model.

Does anyone know of singular location that outlines the difference between the the various rounding functions as they relate to the data types?
Each SQL TSQL, Access Query Functions, and Excel have different ways to accomplish the goal.
Just trying to document for sanity, how to track these differences.

Here are some examples:
MSAccess:
Round(6.5) = 6 'note it didn't make it up to 7
Round(6.49) = 6 ' no surprise given the above
Round(6.51) = 7 'note now we go with 7

Int() or Fix() will round down:
Int(6.9) = 6
Fix(6.99) = 6

Watch out for Negative - Down doesn't mean towards zero:
int(-6.4) = -7
int(-6.6) = -7
fix(-6.4) = 6
fix(-6.6) = 6

MS Excel has a really nice Floor and Ceiling function.
To put this in perspective with Access functions:
fix() <--> Floor()
fix()+1 <--> Ceiling()

In T-SQL - there is Round, Floor, Ceiling
ROUND - Rounds a positive or negative value to a specific length and accepts three values: Value to round, Positive or negative number
( Note: data type can be an int (tiny, small, big), decimal, numeric, money or smallmoney), Precision when rounding
Positive number rounds on the right side of the decimal point
Negative number rounds on the left side of the decimal point
Truncation of the value to round occurs when this value is not 0 or not included
CEILING - Evaluates the value on the right side of the decimal and returns the smallest integer greater than, or equal to, the specified numeric expression and accepts one value: Value to round

FLOOR - Evaluates the value on the right side of the decimal and returns the largest integer less than or equal to the specified numeric expression and accepts one value: Value to round

TSQL Example:

DECLARE @value numeric(10,10)
SET @value = .5432167890
SELECT ROUND(@value, 1)  -- 0.5000000000 -- didn't go to 1 like Access Round
SELECT ROUND(@value, 2)  -- 0.5400000000
SELECT ROUND(@value, 3)  -- 0.5430000000
SELECT ROUND(@value, 4)  -- 0.5432000000
SELECT ROUND(@value, 5)  -- 0.5432200000
SELECT ROUND(@value, 6)  -- 0.5432170000
SELECT ROUND(@value, 7)  -- 0.5432168000
SELECT ROUND(@value, 8)  -- 0.5432167900
SELECT ROUND(@value, 9)  -- 0.5432167890
SELECT ROUND(@value, 10) -- 0.5432167890
SELECT CEILING(@value)   -- 1
SELECT FLOOR(@value)     -- 0

In MS Excel
Numbers can "round" in two ways.

First, they can round up or down. With this type of rounding, numbers get larger when they are rounded up, and smaller when they are rounded down.

Second, numbers can round towards zero or away from zero.
When positive numbers are rounded towards zero, they get smaller; when negative numbers are rounded towards zero, they get larger.

Although Microsoft uses "round up" and "round down" to describe both types of rounding, we use "round up" or "round down" to describe the first type of rounding. And we use "round towards zero" or "round away from zero" to describe the second type.

In Excel 2013: See Ceiling.MATH functions, Floor.MATH Functions.

Each of the SQL Server, MS Access, Excel examples can be found independently. If someone has a good guide, maybe with a chart, on how they relate to each other, please share. I will be aROUND() the FLOOR() for your suggestion.

 

[pbaldy]

I can't think of a site that compares them all, but wanted to clarify regarding:

Round(6.5) = 6 'note it didn't make it up to 7

Access uses bankers rounding, which rounds numbers ending in 5 to the nearest even number, up or down:

?round(7.5)
8
?round(6.5)
6

 

[Rx_]

GASP! I thought you were making fun of some typo that I made on the post.
It really actually does that!

In "banker's rounding"—in its most common form—the .5 to be rounded is rounded either up or down so that the result of the rounding is always an even number. Thus 2.5 rounds to 2.0, 3.5 to 4.0, 4.5 to 4.0, 5.5 to 6.0, and so on.


Looked it up:
Excel Rounding Functions
Excel's ROUND() function performs arithmetic rounding.

ROUNDUP() and CEILING() round numbers up and in so doing move them away from zero. Eg Excel rounds up -2.5 to -3.0.

ROUNDDOWN() and FLOOR() round numbers down and in so doing move them towards zero. Eg Excel rounds down -2.5 to -2.0.

The INT() function moves positive numbers towards zero, and negative numbers away from zero, as it rounds a number to the nearest whole-number (integer) equivalent.

You use the FIXED() function to return a number as text. In making the conversion the function rounds in the same way as ROUND().

There are no functions in Excel for alternate, banker's or random rounding.

Learned something new today.

Here is the MSDN story on it:
http://blogs.msdn.com/b/ericlippert/archive/2003/09/26/bankers-rounding.aspx

Note the last paragraph: Int rounds down and Fix rounds positive numbers down, negative numbers up.

Why did bankers do this?
An effect of Bankers' Rounding. Any interest paid out is odd, while loans are even? It's to get that extra half after rounding.

No wonder I can't get ahead in life! LOL

 

[pbaldy]

I wouldn't make fun! Okay, I would but I'd have added a smiley.

My guess at the purpose is that if you always round .5 up, you probably end up overstating the ending value over a large enough volume of data. When you round some up and some down, the total over a large data-set would be closer to the un-rounded total than if all were rounded up. You can see what it would do in the example I posted. Both rounded up is 15, true total 14, bankers rounding total 14.