Round to Even (aka Banker''s Rounding) - The final function

Question

Round to Even (aka Banker''s Rounding) - The final function

Viewing 15 posts - 286 through 300 (of 378 total)

You must be logged in to reply to this topic. Login to reply

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 1

Sergiy, it was actually you who took this thread off-topic. In the first paragraph of the first post to this thread David wrote:

"If you think banker's rounding is stupid, we have a thread for you to voice your opinion here. Please keep it out of this one". You've been off-topic for weeks.

Apart from that, in my opinion you're behaving like a troll (http://www.teamtechnology.co.uk/troll-tactics.html) and I don't want to engage with you any further.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Cath,

is it so hard to say what you have learnt about mathematics from this thread?

Or you appeared here just to show you are a troll?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 3

I see, you cannot object my proof about digital representation error, but you are too "type A" to admit you're wrong.

Suck it in.

P.S.

Site of NZ Inland Revenue Department operates around the clock.

You may find everything you want there.

_____________
Code for TallyGenerator

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Blimey Cath, that's a real gem! Good find!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Karma-343206 SSCrazy Points: 2104 More actions · Answer 5

http://en.wikipedia.org/wiki/New_Zealand_dollar

uhmm interesting

[wiki]

In 1986, New Zealand adopted Raphael Maklouf's new portrait of the Queen on all its coins. The 1 and 2 cent coins were last minted for circulation in 1987, with collector coins being made for 1988. The coins were demonetised on 1 May 1990. The lack of 1 and 2 cent coins meant that cash transactions were normally rounded to the nearest 5 cents (10 cents as of 2006), a process known as Swedish rounding. Some larger retailers (notably one supermarket chain), in the interests of public relations, elected to round the total price down (so that $4.99 became $4.95 instead of $5.00). Alternatively, many retailers rounded all their prices to the nearest 5 cents to avoid the issue entirely — so a New Zealand shopper often encountered products for sale at prices like $4.95. [/wiki]

Clearly some retailers choose to round up others to round down, no mention of the legality or otherwise of this action, it was a business decision based on the perceived impact on public relations.

[wiki]

Swedish rounding is a method by which money is rounded from a given minimal unit to the closest larger unit represented by physical currency. It is generally a method of rounding which is recommended by a country's Reserve Bank or Retailers Association, however is very rarely a legal requirement to follow.

[/wiki]

Uhmm that would seem to suggest that rounding up is favoured over rounding down however wouldn't that be ripping New Zealand's customers off?

So...

if a New Zealander wanted to buy 1kg of Copper from me at the London Metal Exchange prices http://www.metalprices.com/ which we both agree represent the current market value of Copper How much should i charge them?

I work in $US as i trade mostly with the US. So that 1kg of Copper would cost $US 7.540 [29/June/2007] which is $NZ 9.76198 [29/June/2007] from http://www.xe.com/ucc/convert.cgi which is the site we both agree to use to calculate exchange rates and that all transactions take place at 9:30am using Greenwich Mean Time.

I have access to the ("Le Grand Kilo") in Paris which is the kilogram whatever this weighs is a Kilogram as it's not based on a fundamental physical constant as yet.

I've then used a watt balance to test the mass of my Kilogram of Copper which returned a frequency of exactly (299792458²/66260693)×10⁴¹ Hz.

We both agree that this is an accurate measure of a Kg based on a fundamental constant we then weigh Le Grand Kilo and get a frequency of exactly (299792458²/66260693)×10⁴¹ Hz and we both agree we are happy with this measure.

http://en.wikipedia.org/wiki/Kilogram

Again anyone care to hazard a guess as to how much i should charge the New Zealander considering i only accept cash and they don't have 1p or 2p denominations :crazy

Lots of Love

K.

David Jackson SSCertifiable Points: 6414 More actions · Answer 6

Knowing that, how 1.125 must be rounded to 2 decimal digits?

Well, not the way BR does it.

Why your company have chosen BR for this?

Right, it works for their profits.

It's just a way to cheat on customers, to pay them less than they should get.

You clearly do not take in what you read. We do NOT use BR. This is a curiosity. In my last post I stated

The NOT USED SQL Solution

Also

it was resolved in the client app.

As for cheating customers, try these figures

select round (1.7650, 2) TraditionallyRounded

select round (1.7650, 2, 1) Truncated

-- These is using the first function I posted in *this* thread

select dbo.fn_Bround_1(1.7650, 2) roundToEvenHalf --nearest 50

select dbo.fn_Bround_1(1.7650, 100) roundToEvenPenny -- nearest penny

It's clear to see we overpay as well, but NOT by calculating these figures in SQL, it's done in c#. The algorithm was not written by me, in fact I've not even read it. I'm a DBA manager, NOT a programmer. And why do you keep harping on about 2 digits? That's not the requirement. Read it again and tell me how you would do it.

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Sergiy SSC Guru Points: 110208 More actions · Answer 7

David, I read your statement very thoroughly.

In my last post I stated

The NOT USED SQL Solution

Right. I've got it.

You did not use BR implementation in SQL, it was resolved in the client app.

But you did not said

We do NOT use BR.

Does it really matter where BR is happening?

This is a curiosity. Do you understand your own statements?

Now, what are you trying to prove with your examples?

I never told that BR returns wronf result in every case.

If you would bother to read my posts you'd find that I said it cheats in 900 cases out of 20k.

select dbo.fn_Bround_1(1.7650, 2) roundToEvenHalf --nearest 50

is clearly not the case. In this case BR follows TR and return right result.

Try proper case, e.g. 1.5000, and see the wrong return.

It's clear to see we overpay as well

It's clear that if BR is wrong in 900 out of 20k cases then it reurns the same result as TR in the rest of cases, and in almost half of them it rounds up.

But it does not change the fact that it's wrong in 900 cases.

Read it again and tell me how you would do it.

Do what?

_____________
Code for TallyGenerator

David Jackson SSCertifiable Points: 6414 More actions · Answer 8

We do NOT use BR.

I said we have a problem where some depots do not deal in denominations less than 50 pence. At others it's 10 pence. This saves the hassle & expense of having lots of differing denominations of coins around the place. Our customers think it's perfectly fair to do this, as sometimes they 'win', sometime we 'win'.

So the problem is how to do it. BR was one proposed solution I thought about. But we did not use it, the developer wrote his own solution in a procedual language, which I've not seen. Nor, and trust me on this, do I want to. It may be a BR function, it may be a round down function. I have no idea, but the business are happy that it does its job.

Do What?

Take the figure 1.5650 and return the nearest .50 figure. That's 1.5.

For 1.7650 it is 2.0. But write it to take different factors. That's the whole point.

{Clarity Edit}

So for a factor of 10, the figures above become 1.6 and 1.8. In fact try these

select dbo.fn_Bround_1(1.5650, 2) --nearest 50

select dbo.fn_Bround_1(1.5650, 10) --nearest 10

select dbo.fn_Bround_1(1.5650, 100) -- nearest penny

select dbo.fn_Bround_1(1.7650, 2) --nearest 50

select dbo.fn_Bround_1(1.7650, 10) --nearest 10

select dbo.fn_Bround_1(1.7650, 100) -- nearest penny

You'll get

---------------------

1.5000

1.6000

1.5600

2.0000

1.8000

1.7600

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Sergiy SSC Guru Points: 110208 More actions · Answer 9

We do NOT use BR.

...

It may be a BR function, it may be a round down function. I have no idea

Can you be more consistent?

But write it to take different factors. That's the whole point.

Is it really a problem?

I guess it's one of essential exersises you have to do in order to complete programming courses.

_____________
Code for TallyGenerator

David Jackson SSCertifiable Points: 6414 More actions · Answer 10

Is it really a problem?

Yes. I have no idea what the real amounts are, but lets play.

Imagine you are in a business that does 1,000,000 cash transaction a week/month/period.

Let suppose that 80% of those transactions end up with you having to give some one a penny in their payment.

Thats 800,000 transactions. That's 8000 bags of pennies. At 10% charge per bag, that's 800 GBP per million transactions.

I can't tell you how many transactions I've got in my db, but trust me, its a fair few more than a million.

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Sergiy SSC Guru Points: 110208 More actions · Answer 11

I told about programming solution which performs TR to 20 or 50 cents.

So,

Is it really a problem???

_____________
Code for TallyGenerator

David Jackson SSCertifiable Points: 6414 More actions · Answer 12

Is it really a problem???

If you have written a solution, there must have been a problem to start with.

Solutions exist to solve problems, remember? And lots of people reading these forums come looking for solutions, I certainly did when I started to learn SQL.

I've told you before, I'm no mathmatician. I'm no graduate either, as I did not have the benefit of a tertiary education. But I like to learn and, where I can, help out. I wish all felt the same.

BTW, can you remind me how TR will round down to .50 if the amount is > .50? I can't get my head round it.

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Sergiy,

I have already admitted to being a Type A personality when it comes to my job. I am very confident in my skills as a programmer and DBA. I will also admit when I am wrong. In this case, I am not wrong, so no "I'm sorry, I was wrong" is needed.

I also know when to plead ignorance on a subject, but in doing so, I also take the inititive to learn about the subject so I can discuss it rationally. I'd rather tell someone, especially my boss, I don't know but I'll get back to you when I do; rather than act like I know what I am talking about. It comes down to credibility.

So, back to my last question, do you have the citation in NZ Law that says using the Bankers Round is illegal yet? I spent several hours last night on the web and couldn't find a thing. If it is there, show it.

David Jackson SSCertifiable Points: 6414 More actions · Answer 14

For those interested, I changed the factor parameter to be what you want your figure rounded to, as I was never very happy that you had to pass 2 to round to the nearest .50. For those of you outside the UK or those younger than my delicate years, 'bob' is English slang for 5 pence.

select dbo.fn_Bround_1(1.5650,1) [Penny]

select dbo.fn_Bround_1(1.5650,5) [1Bob]

select dbo.fn_Bround_1(1.5650,10) [2Bob]

select dbo.fn_Bround_1(1.5650,20) [4Bob]

select dbo.fn_Bround_1(1.5650,25) [5Bob]

select dbo.fn_Bround_1(1.5650,33) [Third] -- couldn't resist

select dbo.fn_Bround_1(1.5650,50) [10Bob]

select dbo.fn_Bround_1(1.5650,100) [Pound]

Dave J

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

ALTER FUNCTION FN_BROUND_1

(@p1 DOUBLE PRECISION,

@p2 INT)

RETURNS MONEY

/*

Round-to-even method (from http://en.wikipedia.org/wiki/Rounding)

This method is also known as unbiased rounding or as statistician's rounding or as bankers' rounding.

It is identical to the common method of rounding except when the digit(s) following the rounding digit

start with a five and have no non-zero digits after it. The new algorithm is:

* Decide which is the last digit to keep.

* Increase it by 1 if the next digit is 6 or more, or a 5 followed by one or more non-zero digits.

* Leave it the same if the next digit is 4 or less

* Otherwise, all that follows the last digit is a 5 and possibly trailing zeroes;

then change the last digit to the nearest even digit. That is, increase the rounded

digit if it is currently odd; leave it if it is already even.

With all rounding schemes there are two possible outcomes: increasing the rounding digit by one or

leaving it alone. With traditional rounding, if the number has a value less than the half-way mark

between the possible outcomes, it is rounded down; if the number has a value exactly half-way or

greater than half-way between the possible outcomes, it is rounded up. The round-to-even method is the

same except that numbers exactly half-way between the possible outcomes are sometimes rounded up—sometimes down.

Although it is customary to round the number 4.5 up to 5, in fact 4.5 is no nearer to 5 than it is to 4

(it is 0.5 away from either). When dealing with large sets of scientific or statistical data, where trends

are important, traditional rounding on average biases the data upwards slightly. Over a large set of

data, or when many subsequent rounding operations are performed as in digital signal processing, the

round-to-even rule tends to reduce the total rounding error, with (on average) an equal portion of numbers

rounding up as rounding down. This generally reduces the upwards skewing of the result.

Round-to-even is used rather than round-to-odd as the latter rule would prevent rounding to a result of zero.

Examples:

* 3.016 rounded to hundredths is 3.02 (because the next digit (6) is 6 or more)

* 3.013 rounded to hundredths is 3.01 (because the next digit (3) is 4 or less)

* 3.015 rounded to hundredths is 3.02 (because the next digit is 5, and the hundredths digit (1) is odd)

* 3.045 rounded to hundredths is 3.04 (because the next digit is 5, and the hundredths digit (4) is even)

* 3.04501 rounded to hundredths is 3.05 (because the next digit is 5, but it is followed by non-zero digits)

-- =============================================

-- Example to execute function

-- =============================================

SELECT dbo.fn_BRound (282.26545, 100) -- 100 to nearest penny, 20 to nearest 5 pence, 10 to nearest 10 pence,

-- 5 to nearest 20 pence, 2 to nearest 50 pence, 1 to nearest pound/dollar/euro

Code (poorly?) converted from VB example @ http://support.microsoft.com/kb/196652

*/

AS

BEGIN

DECLARE @Temp DOUBLE PRECISION,

@FixTemp DOUBLE PRECISION

SELECT @p2 = 100 / @p2

SELECT @Temp = @p1 * @p2

--This is the closest I can get, but it is still wrong for certain values

SELECT @FixTemp = SIGN(@Temp + 0.5 * SIGN(@p1)) * FLOOR(ABS(@Temp + 0.5 * SIGN(@p1)))

-- Handle rounding of .5 in a special manner

IF @Temp - FLOOR(@Temp) = 0.5

BEGIN

IF @FixTemp / 2 <> FLOOR(@FixTemp / 2) -- Is Temp odd

-- Reduce Magnitude by 1 to make even

SELECT @FixTemp = @FixTemp - SIGN(@p1)

END

RETURN @FixTemp / @p2

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

http://glossopian.co.uk/
"I don't know what I don't know."

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 15

I’ve seen stupid flame wars on this site, but this is the most ridiculous thread ever.

Give it a rest. Really.