feather

Rounding to the nearest 100, 1000, and so on in Velocity

Level 10 - Community Moderator
Level 10 - Community Moderator

featherSo you've got someone's Loyalty Points stored in an Integer field.[1] Or maybe it's their Year-To-Date Store Purchases, or some other precise number.

 

But to be more casual, you don't want to display the exact number in an email, just the nearest 100 points or 1000 dollars. Or maybe you just want to output a friendly name for their current tier (Gold Member, Bronze Member, etc.) which means you need their nearest-whatever under the hood.

 

How do you compute such a thing accurately? Ask your math-conscious friend and they'll probably say…

 

Just divide the number by 100, round to the nearest integer, and multiply by 100.

… and walk away smugly. And in the meat-world sense, they're correct.

 

They mean a formula like this:

round( loyaltyPoints / 100 ) × 100

 

And if you do that calculation on the back of a napkin, it works.

 

For example, if I've got 122,122 points:

122,122 / 100  ⟶ 1,221.22
round(1,221.22)   ⟶ 1,221
1,221 × 100     ⟶ 122,100

 

That's what we want, right? 122,100 is 122,122 to the nearest 100.

 

Let's check with 55,550 points:

55,550 / 100    ⟶ 555.50
round(555.50)      ⟶ 556
556 × 100       ⟶ 55,600

 

Lookin’ good!

 

Except you forgot to call after them, “But what if I'm, uh… using a computer? Does that matter?” Spoiler: Yes, it matters.

 

Good effort!

You scribbled down your friend's algorithm (simple as it is). Now you have to write the code. That's where things get much harder than you think. And this isn't just hard in Velocity: it's a lowkey tripper-upper in other languages, too.

 

You'll probably open the Velocity MathTool docs and see, to your joy, that $math has methods for all parts of the formula: divide, round, and multiply. You have to do some ugly nesting as usual in Velocity, but this should do exactly what your friend advised:

 

 

#set( $nearest100 = 
  $math.mul(
    $math.round(
      $math.div(
        $lead.loyaltyPoints,
        100
      )
    ),
    100
  ) 
) 

 

 

 

You plug in 122,122 points. It works: $nearest100 is 122,100.

 

You plug in 55,550 points. It works: $nearest100 is 55,600.

 

You plug in 26,250 points. $nearest100 is 26,200. What the??? It should be 26,300! As far as you know, “nearest 100” means whatever-50 rounds up to 100, and 250 rounds up to 300, just like 55,550 rounds up to 55,600.

 

“I was so close!” you scream. (If it’s any consolation, you weren't that close.☺)

 

What just happened?

You just hit an unpleasant wall called Half Even Rounding.

 

Half Even Rounding goes by several other names: the most popular one is Banker's Rounding. It’s a established rounding method with beneficial properties (more on those in a moment). But it’s not what laypeople think of as rounding — nor even what a lot of technical people think of! — and it’s not what your boss meant when they said “show their point count to their nearest 100.”

 

Yet Half Even is what Velocity's $math.round does under the hood (unlike Java's very-similarly-named Math.round, and the method of the same name in JavaScript, too).

 

How Half Even is done

Half Even rounding is easy to explain (skipping over why it exists for the moment).

 

With Half Even, if a decimal number sits halfway between two numbers — the most obvious one being .5, though it also applies to .05, .005 and so on — rather than rounding that half upward (that's Half Up, a.k.a. Arithmetic Rounding, a.k.a. regular ol’ rounding) the number is rounded to the nearest even number. So 1.5 rounds to 2, and 2.5 rounds to 2 as well. 0.5 rounds to 0. 3.5 rounds to 4.

 

In all other cases but the halfway boundary, Half Even/Banker’s behaves identically to Half Up/Arithmetic: .49 rounds to 0, 1.49 to 1, 1.51 to 2, 2.49 to 2, 2.51 to 3. It's only the boundary case that's special.

 

Clearly we don’t want the resultant confusion in a marketing email. A CS major, if one wandered onto your floor, would start to mathsplain...

 

AKSHUALLY, Half Even is provably better over a wider data set—

... but they’d be rightfully cut off. This is martech, after all.

 

But let me try to explain when Half Even would be preferable, using a slightly different example from others I've seen on the web.[2] (Yes, I'm refusing to just skip this part.)

 

Why Half Even is done

If you've ever gone grocery shopping on a budget, you've probably used Arithmetic Rounding when filling your basket. You put an $1.25 item in, you tally a 1 in your head, you put a $1.89 item in, you add a 2.

 

If the store uses that hackneyed trick of pricing stuff at $something.49, you might adopt a modified version of Arithmetic where $2.49 rounds up to 3 in order to avoid downward bias in your approximate total. (At my local Nordstrom Rack, I would do better to round up to the nearest $100, my long-suffering partner would agree!)

 

In the world of personal finance, it's great if your in-your-head total is well above the real total when you check out: the rest is beer money. But in the capital-F Finance world, a pronounced upward or downward bias is unacceptable.

 

There’s something else you probably disregard when grocery shopping: even if store prices were evenly distributed, using all possible whole and fractional prices with equal frequency, there would still be an upward bias with Arithmetic Rounding.

 

Why is there a bias? Because for every 100 non-zero values, 49 of them round down (1-49) and 50 of them round up (50-99). So there’s always a slightly higher chance that the sum-of-rounded-values will be larger than the exact sum.

 

The bigger problem is that the numbers you're rounding and tallying are rarely, if ever, evenly distributed across the range like that.

 

Take an olde-fashioned candy shop where every piece is $0.25. That means each dollar range has only 3 values in practice ($n.25, $n.50, $n.75) and 2 out of the 3 round up using Arithmetic Rounding. Now you've got a major upward bias.

 

So: Half Even/Banker’s rounding corrects for that bias by alternating the rounding direction based on the preceding digit. It works great for financial data, but nevertheless there isn’t a single Best Rounding Method Ever for all possible data sets (much as there is no best sorting algorithm). And it sure as heck is weird to want Half Even in a marketing email. I’m going to go out on a limb and say 100% of the time, if you’re reading this blog post, you want Half Up/Arithmetic.

 

Half Up rounding in Velocity

So now you get the reason that both Half Even and Half Up exist, at least.

 

As for why Velocity’s tantalizingly named $math.round is implemented with Half Even as the only option... I assume the idea was if you’re using MathTool at all, you’re most likely to be outputting tabular financial data (on a website obviously, not an email, remember Velocity isn’t just for email!).

 

Anyway, there’s at least a hint about the implementation in the docs for $math.round:

 
 

5c0c6ba9692c2000bf71e6ff_mathtoolround_mathtooldoc

 

 

See Also is an understatement! The phrase implies “See this similar thing that might be interesting” but here it's “See the actual method used under the hood.”

 

Rather than using Java's identically-named Math.round, it calls Math.rint which has the pesky characteristic of doing Half Even rounding. In other words, $math.round and Math.round are not the same.

 

Here’s Velocity:

> $math.round(2.50) ## uses java.lang.Math.rint
⋖ 2

 

Here’s Java:

> Math.round(2.50); // uses java.lang.Math.round
⋖ 3

 

Interesting attempts that won’t work

If you weren’t trying to round to the nearest 10, 100, etc. (that is, whole numbers to the left of the decimal point), but instead numbers to the right of the decimal point only, you could use $math.roundTo — which does Half Up by default.

 

But roundTo is built for the right side. It’ll round 9194.5 to 9194.6 or 9194.6 to 9199.7. But it can’t round 9194.5 to the nearest 10 (9190) or nearest 100 (9200).

 

Another tactic is setting the HALF_UP rounding mode on $number.format. This works for outputting both 9194.50 and 9194.60 as $9195.00, for example:

 

 

#set( $currencyFormat = $number.getNumberFormat("currency", $convert.toLocale("en_US")) )
currency w/banker's: ${currencyFormat.format($lead.SomeField)}
#set( $void = $currencyFormat.setRoundingMode($field.in($currencyFormat.getRoundingMode()).HALF_UP) )
currency w/arithmetic: ${currencyFormat.format($lead.SomeField)}

 

 

 

But this, too, won’t meet our initial goal of Half Up rounding to the nearest 10 and so on. (AFAIK, there’s no SimpleNumberFormat symbol that does rounding on the left-hand-side.)

 

The one that works

The solution took me a long while to figure out, principally because it requires an instance of BigDecimal (note: not BigInteger) and long ago (in Marketo-years) Marketo removed the ability to dynamically instantiate a new object of one’s choosing from Velocity.

 

You need a BigDecimal because to do rounding losslessly, you can’t risk doing floating-point multiplication and division in order to shift the decimal point back and forth. You want to truly move the point left and right, with every bit unchanged. BigDecimal has methods for that!

 

So, without further ado, your new friend, the Velocimacro nearest10N_v1:

 

 

#macro( nearest10N_v1 $original $format $to10N )
#set( $formatObj = $number.getNumberFormat($format, $convert.toLocale("en_US")) )
#set( $void = $formatObj.setParseBigDecimal(true) )
#set( $bigDec = $formatObj.parse( $number.format($format,$original.toString())) )
#set( $bigDecRounded = $bigDec.movePointLeft($to10N).setScale(0,$field.in($bigDec).ROUND_HALF_UP).movePointRight($to10N) )
${number.format($format, $bigDecRounded)}##
#end

 

 

 

nearest10N_v1 takes 3 arguments:

  1. The original number.
  2. The desired output format. (The format string is also used for some internal sorcery, but you need not worry about that.)
  3. The Log10 to round to. That is, 1 = nearest 10, 2 = nearest 100, 3 = nearest 1000. (Since there’s no Log10 function to draw on within the macro, unfortunately you can’t just pass 10 or 100. Maybe in a later version I’ll figure that out!)

 

You can call it with a named format like "currency" or "integer":

> #nearest10N_v1( 9194.25, "currency", 2 )
⋖ $9,200.00

 

Or pass it a custom format of your choosing (remember, the easy-to-read ##,### needs to be escaped in Velocity, since ## is a super-special sequence in VTL):

> #nearest10N_v1( 9194.25, "${esc.h}${esc.h},${esc.h}${esc.h}${esc.h}", 1 )
⋖ 9,190

 

Enjoy!

 


Notes

[1] Your regular reminder to avoid using Currency or Float fields in Marketo.

[2] One particular StackOverflow answer on the topic is really superb — SO has wrong answers on countless topics, but this one is the real deal — and it’s from a mathematician who’s exponentially more knowledgeable than yours truly. But I’m trying for a new angle, since there are tons of “Half Even just is better, OK?” evasions still out there.

51
0