Rounding up formulas 1

[azwaan]

i'm using CR 8.5 and i have a set of numbers which i want rounded up.

i used the format menu to round..it but it seems to add to the higher number when the number is a 5.

for eg: 5.875 rounded up to 2 decimal places is 5.88
which is correct , because the preceeding digit before 5 is 7 and is an odd number.

5.865 rounded up to 2 decimal places is also 5.87 which is incorrect because the preceeding number is 6 , an even number.

anyway to get around this problem?

 

[dgillz]

I disagree with your initial statement "5.865 rounded up to 2 decimal places is also 5.87 which is incorrect because the preceeding number is 6, an even number".

While 6 is indeed an even number, are you saying that even numbers should be rounded down and not up? What if the digit in question was 8, also an even number? What if the digit in question was a 1, an odd number, should it be rounded up?

In my experience, anything .50 or higher is rounded up, and anything less than .50 is rounded down. If this is not what you want please supply a full set of rules and I am sure we can come up with something.


Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[azwaan]

YES I AM SAYING that even numbers should be rounded down, as it is the practice i have been thought, and widely used atleast in my part of the world.

if the number is **.50, i want numbers rounded up if the preceeding number is only an odd number.

hence 8.675 should be 8.68 but 8.465 should be 8.46

this is the behaviour displayed by the visual basic round function in my experience. and i want crystal to display the same kind of behaviour.

 

[dgillz]

so 12.865 rounds to 12.86, but 11.865 rounds to 11.87? Have I got this right?

I disagree that the round() function in VB does this - at least in North America. I have truly never heard of rounding this way, assumming I understand it correctly.

You have the option of using VB syntax in your crystal reports formulas, have you tried that?

If that does not give you the desired results, we can create a formula to determine if the last whole number before the decimal is odd or even:

If truncate({YourField)-round(truncate({YourField}/10)*10) in [1,3,5,7,9] then "odd" else "even"

Then create 2 different rounding scenarios based on this field and put them in an if-then.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[lbass]

axwaan--

I'm with you--that's the way I learned to round as well, and I was surprised that the round function doesn't work that way. Here is a formula that you could use instead of your number field, assuming {table.no} has three decimals:

if remainder(val(mid(totext(table.no,3),instr(totext(table.no,3),".&quot+2,1))
,2) = 0 and
right(totext(table.no,3),1) = "5" then
round(table.no,2)-.01 else
round(table.no,2)

-LB

 

[dgillz]

lbass, you are British are you not?

Azwaan what about you, where are you from?

This is fascinating to me. I have never heard of a different way to round. Of course I have only been in the USA, Mexico and Canada.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[lbass]

dgillz,

I'm from Wisconsin->California->Massachusetts. I think the method is designed to prevent systematic incorrect increases due to rounding--the same thinking as rounding up over 5 and down under 5. I just checked Excel, and it rounds up on a preceding even number followed by "5", also (to my surprise). Maybe it's a generational thing, and what I learned is "old school." Just asked my son, who had never heard of the odd-even method either. But then I did find it through an Internet search as the "Odd-Even Rounding Rule" in a chemistry curriculum at a community college.

-LB

 

[dgillz]

lb,

I am 41, so are you older than my generation? It blows me away to think this is an established way to round. Maybe someone should write a UFL for it.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[lbass]

dgillz,

Well, I'm not old enough to be your parent, but yes, I'm older than that, though psychologically I hover around 23. Still, there must be a reason that applications have chosen not to use the convention I learned. Maybe it's seen as unnecessarily complex?

-LB

 

[azwaan]

ok..looks like i have a lot of questions to answer..

first up..to Dgillz..

"12.865 rounds to 12.86, but 11.865 rounds to 11.87? Have I got this right?"

no..this is wrong..

12.865 rounds to 12.86 while 11.865 also rounds to 11.86 as well..it's not the number preceeding the decimals that u check..

it's the number before the position of rounding that u need. because i am rounding to 2 decimal points here , i am using the number 6 to evaluate it..cos 6 is 2 decimal point after the integer.


to your next question..i'm from south asia and everybody else seems to use this formula to round up here, and the VB round function displays this behaviuor too.

another thing i should mention is that i am using the format menu's Rounding option to round the number. i am not using any formula.should i write a special formula to accomplish what i need? or is there an option to change this kind of behaviuor.

about being from an older generation ? i really don't know that u would call a 21 yr old that.

 

[silverfish12]

I know this is a bit off from the original message.
I used the Round function for my field (the value is 2003, data type is integer) & in the report, it came out as 2,003.00.
I want it to be displayed as 2003 without the comma & precision. I've tried all the functions: Int(), CStr() but it still come out as 2,003.00.
I'm getting frustrated with Crystal Report 8.5.

 

[dgillz]

Silverfish,

If this is a number, just format it. Right click the field, select format field, go to the number tab and choose one of the listed examples which has no thousands separator and no decimals.

If this is not a number field, please let me know more.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Madawc]

Regarding rounding, all of the computer systems that I know round 5 upwards. As lbass says, varying according to odd or even does avoid systematic errors, a five-to-four ratio of fractions rounding up rather than down.

I suspect that early computers simplified things by always rounding up 5 rather than checking the next digit. And like many early decisions, we are stuck with them.

Madawc Williams
East Anglia, Great Britain

 

[GMcNamara]

This is called Bankers Rounding. With Bankers rounding, values below 0.5 go down and values above 0.5 go up. Values of exactly 0.5 go to the nearest even number. So 12.5 will be rounded down to 12 and a value of 13.5 will be rounded up to 14.

The justification for bankers rounding is as follows. Take the numbers 12.0 to 13.0 in steps of 0.1. There are 9 values that need to be rounded. Traditional rounding moves 5 of the 9 values up and 4 down - always. This is biased (1/9 more up than down). By moving 0.5 to the nearest even number it is now exactly 9 of the 18 numbers in each direction. It might not sound like much, but if it relates to interest calculations on billions of dollars, the difference in rounding calculations is still worth many millions of dollars.

I don't think Crystal has a specific bankers rounding function. I am a bit surprised that Visual Basic is using this as the default. What version are you using?

Silverfish, use the ToText function to display your number. It accepts several different arguments i.e. ToText({Table.NumberField}, '', 0). The first argument is obviously your integer field. The second argument is the thousands seperator, which in this case is nothing. The third argument is the number of decimal places you would like displayed.

 

[silverfish12]

dgillz,

It is a number field but I need to concatenate it with another field, so, I need to use the formula field to do this & I can't right click to format it.

GMcNamara,
I tried ToText({Table.NumberField}, '', 0) and I've got this error:
Error in formula <Test>. 'Totext({CBCSpReportBNM.FinancialYr}, '',0)' Bad number format string.

 

[lbass]

A slight correction to the formula will make it work:

ToText({Table.NumberField},0,&quot;&quot

-LB

 

[GMcNamara]

lbass is absolutely right. The number of decimals should come before the thousand seperator. Also, I used two single quotes where he used double quotes. My example looked like one double quote.

 

[silverfish12]

Yes, I got it working by using lbass' formula.
Thanks.