Tangential comments about Software Development

Sunday, March 19, 2006

On Rounding

This is just a wee bitch about Microsoft's permanent need to improve our programming lives. In this case, it's about rounding numbers to two decimal places.

Excel Rounding

In the UK, Value Added Tax (VAT) is 17.5%. If something costs £2.00 then £0.35 is added in VAT making a total paid of £2.35. If something costs £2.20 then 17.5% is £0.385 which either £0.38 or £0.39 depending on whether you round up or round down.

At school, I was taught to round up if the number ended in 5. And that's what the Excel rounding routines (see above) do. It's also what the SQL Server ROUND function does - see below.

SQL Server Rounding

It's not what the .Net Framework rounding does - they have decided to use Bankers Rounding, where you round towards an even number. So 0.385 is rounded to 0.38. Bankers Rounding must be cockney rhyming slang for Astounding.

At least .Net looks consistent. Imagine my surprise when I checked this in Visual Basic 6.0 to find that some roundings go one way, some go another. How else to explain that the VAT on 0.20 is 0.03, but the VAT on 4.20 is 0.74? It is pretty stink, as they say in New Zealand.