Yesterday a friend asked an interesting question. Normal rounding is based on the rule we are familiar with. Anything decimal point that is 5 and higher will round up to the next number. So 11.5 becomes 12. My friend though had a client who wanted to change the rules. Instead of rounding on 5, they wanted to round on 4. So 11.4 would become 12. 11.39 would round down to 11. Sounded weird to me, but maybe that's what the Wall Street folks have been doing lately.

I whipped up a quick function that treated numbers like strings:

`function myRound2(x) { if(listLen(x,".") is 1) return x; if(left(listLast(x, "."),1) >= 4) return incrementValue(listFirst(x,".")); return listFirst(x,"."); }`

This worked fine until he tested with a negative number. He came back with a nicer solution (makes me wonder if he will ever ask me again for help!) that worked in all cases:

`function myRound(x) { if (abs(x - int(x)) >= .40) return ceiling(x); else return int(x); }`

His solution correctly handled the negative numbers. You could even make it a bit more abstract:

`function myRound(x,y) { if (abs(x - int(x)) >= (y/10)) return ceiling(x); else return int(x); }`

This will let me pass any value for the rounding limit.

Anyway, I'll use the above as a good springboard to remind folks about unit testing. When he found the issue with my code and negative numbers, it would have been the perfect place to write a unit test to ensure that never became a problem again.

## Archived Comments

nice - "Sounded weird to me, but maybe that's what the Wall Street folks have been doing lately"

Agreed. A little weird.

Your friends needs to make sure he gets paid before this fishy business rule brings down their business. :)

I was just discussing CF's lack of a good "Round To X Decimal Places" function. We had run into the classic "internal FPU rounding error", but we were dealing with Money so we needed it properly rounded to 2 decimal places. We ended up doing "myMoney = ROUND(myMoney*100)/100", but of course that seems kind of kludgy. There are several ways of getting to Integers (Round(), Int(), Fix(), Ceiling(), etc), but none that deal with precision of a decimal place. And unfortunately both NumberFormat() and DollarFormat() simply truncate off any characters past the mask with no rounding.

Well, he could Nutritional Information rounding; .999999 calories = 0 CALORIES! 1.99999 servings in a container = 1 Serving!

Actually, I just remembered an interesting rounding issue I ran into last week. I have a calendaring app here, and modified it so that users can upload events using an Excel file as a template. I'm using Ben Nadel's POI cfc to work with the Excel files (works awesome). Thing is, a date/time field in Excel, after being mangled through POI into a query, returns a floating-point numeric representation of the date, using "time begins in 1900-01-01" format.

Now the weird thing is, rounding definately messes up the time! If somebody has a date/time in Excel of "2009/04/01 4:00 pm", it'll translate in CF as "2009/04/01 3:59:59 pm". I slapped together a UDF and submitted it to cflib.org to correct for this quirk of rounding when handling date/times.

So yeah, rounding (and floating-point arithmetic in general) is not without it's quirks.

That is a bit strange, i have dealt with rounding where if there is any remaining decimals always round up, in which case I also had to look at the number as a string and did something very similar.

I've never heard of anyone rounding numbers like that before. Weird indeed.

Thanks a lot, about the comment above its nor weird at all, specially talking about wight units and shippings, for example UPS consider 1.1 still like 1 Kilo and 1.2 like 2 Kilos.

A.

Thanks, Ray!

That gave me the mindset I needed to figure out how to round up to the next 10. I am writing a little conversion from Access 2010 to SQL Server 2012 and needed to make sure the new varchar columns would be big enough for the copied-in data.

Once I get "y" (length of longest string in the field), I can get the next higher column size in increments of 10 via #ceiling(y/10)*10#