-
Jan 4th, 2018, 05:38 PM
#1
Thread Starter
Hyperactive Member
[RESOLVED] Round Value related query ...
The following code is fine except one small modification is required:
Code:
"Select A2,A1,A37,A4,A5,A6,SUM(A20),SUM(A25),A26,SUM(A27),A28,SUM(A29),SUM(A31),-Int(-SUM(A31)+.5) AS AmountRounded from NewSalesDBTable GROUP BY A2,A1,A37,A4,A5,A6,A26,A28 ORDER BY A1 ASC"
It rounds the decimal values upto .50 as Value -1 and above 0.50 as Value +1.
Example:
105.49 = 105.00 ------------It's okay
105.50 = 105.00 ------------But here it should be 106.00
0.50 & above should be added as 1Rupee to the total amount.
How to mdifiy it?
-
Jan 4th, 2018, 06:55 PM
#2
Re: Round Value related query ...
Breaking this down, working from the inside out.
-Int(-SUM(A31)+.5)
Let's use your example of the result of the Sum being 105.49, and then 105.50.
-Int(-SUM(A31)+.5)
-Int(-105.49 + .5)
-Int(-104.99)
-(-105)
105, sure, that works as expected
-Int(-SUM(A31)+.5)
-Int(-105.50 + .5)
-Int(-105.00)
-(-105)
105, which is not properly rounded up as you noticed
Now, what if you don't use double-negatives?
Int(SUM(A31)+.5)
Int(105.49 + .5)
Int(105.99)
105, that works as expected
Int(SUM(A31)+.5)
Int(105.50 + .5)
Int(106.00)
106, also works as expected
Get rid of the double-negatives.
Last edited by OptionBase1; Jan 4th, 2018 at 10:07 PM.
-
Jan 4th, 2018, 10:34 PM
#3
Re: Round Value related query ...
Just for additional clarity, I thought I would add some more comments. Because it might be tempting to think back to high school math days when we were all taught (correctly) that a negative of a negative value is the same as the positive value, and you might look at the formula above and think that the two negatives will cancel each other out in a way that should produce the exact same answer as if neither of them were there. The trick comes in to the definition of rounding up and negative numbers.
The int(number + 0.5) rounding formula that has probably been taught in all entry level programming classes for decades rounds up at (interger).5. The key term there is up.
It is easy to see -105.4 and round it (correctly) to -105 and *think* we're rounding down.
Or to see -105.6 and round it (correctly) to -106 and *think* we're rounding up.
Well, in the first case, we're actually rounding up, and in the second case, we're actually rounding down. Because we're dealing with negative numbers here the definition of up is the number that is closer to 0, and the definition of down is the number further away from 0.
So at the value -105.5, the rules of rounding say we round up, meaning the resulting number is closer to 0, or -105
That is why -int(-number+0.5) doesn't round up properly for values with a decimal of exactly .5, although it does properly round for all other values.
Last edited by OptionBase1; Jan 4th, 2018 at 10:40 PM.
-
Jan 5th, 2018, 03:58 AM
#4
Re: Round Value related query ...
do not put off till tomorrow what you can put off forever
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|