Bankers Math

[spl]

I sent the code below to an old WB user who asked about how WB handled Bankers math [didn't find much on that and udf I wrote in 2010 was ugly], i.e. rounding up or down a midpoint number to the closest even number. I found the CLR helpful, but struggled a bit with Message() presentation. Couldn't keep the original decimal number as well as the Banker number (with no decimals, the 2nd parameter of the conversion) w/out reverting to abs(). This proved useful in terms of correct conversion, but wondered if there was a way to display both w/out reverting to abs()?

Code Select Expand

ObjectClrOption ("useany","System")
rounding = ObjectClrNew("System.Math")
banker = ObjectClrType("System.MidpointRounding",0)
;decimals(0)
r = 3.5
r1 = rounding.Round(r,0,banker)
Message(r:" Bankers Rounded",abs(r1))
r2 = 2.5
r3 = rounding.Round(r2,0,banker)
Message(r2:" Bankers Rounded",abs(r3))
Exit

[td]

For whatever reason, the CLR returns VT_R8 instead of a VT_I8. You can convert it to a 64-bit WIL integer using the Int64 WIL function. However, you may get some unexpected results with either Int64() or Abs() when the numbers reach the size of the national debt. That speaks to why the CLR method returns a VT_R8.

[td]

It does point to an interesting problem. WIL does not ever try to convert large VT_R8 variants to INT64s. This is not correct and it should be fixed in the next release.  Thanks for the post.

[td]

You can also ensure no overflow by using the following:

Message(r:" Bankers Rounded",ObjectType('i8',r1))

[spl]

You can also ensure no overflow by using the following:

Message(r:" Bankers Rounded",ObjectType('i8',r1))

Thanks. Actual ask was about processing 300k rows at a time and a formula for bankers math. So, running original script with WB(64) would still necessitate either abs() or ObjectType()?

Interesting what you said about the [cynical?] debt limit. The System.MidpointRounding enum has only 2 values up to .NET 4.8 [which is CLR for PS 5.1]. But in .NET9 [or PS 7] the values are expanded

ToEven
0   
The strategy of rounding to the nearest number, and when a number is halfway between two others, it's rounded toward the nearest even number.

AwayFromZero
1   
The strategy of rounding to the nearest number, and when a number is halfway between two others, it's rounded toward the nearest number that's away from zero.

;added to .Net9
ToZero
2   
The strategy of directed rounding toward zero, with the result closest to and no greater in magnitude than the infinitely precise result.

ToNegativeInfinity
3   
The strategy of downwards-directed rounding, with the result closest to and no greater than the infinitely precise result.

ToPositiveInfinity
4   
The strategy of upwards-directed rounding, with the result closest to and no less than the infinitely precise result.

Probably how to view Math for 64+, or some programmers inventing values to keep their job.

[td]

Thanks. Actual ask was about processing 300k rows at a time and a formula for bankers math. So, running original script with WB(64) would still necessitate either abs() or ObjectType()?

Only if you wish to store or display the rounded number as an integer.

Interesting what you said about the [cynical?] debt limit. The System.MidpointRounding enum has only 2 values up to .NET 4.8 [which is CLR for PS 5.1]. But in .NET9 [or PS 7] the values are expanded

That bit is fixed for the next release. Large VT_R8s will be converted to 64-bit integers when the R8 is to large to be represented by a 32-bit integer. This is how regular WIL floats are already handled.

[td]

WinBatch did have support for .Net 6/7/8/9 at one time through an extender, but it never got beyound the beta stage because there was zero interest. The amount of support required for ever-changing versions made it prohibitively time consuming as well.

As far as the national depth goes, it was the first real world monetary amount that came to mind that would not fit into a 32-bit integer. I could have also used MSFT's cash on hand or the amount they spent on data centers for training and running AI models.

[spl]

I could have also used MSFT's cash on hand or the amount they spent on data centers for training and running AI models.

or the number of times I have bothered you with questions.