Rounding to the nearest quater

[judyf60]

I am doing some crystal reports(version 8). I am trying to convert the retail price for a product if the customer is from CANADA. The formula I was supposed to use is (retail price/.63)rounded to nearest quater. It worked fine with retail price. Now I am trying to use the same formula to get Accounts statements. The problem, I am facing is if the previous balance is a positive number the rounding is prefect, but if the balance is a negative number the value shown is 0.00. All the fields are number datatype. I cannot change the datatype of any field.

My code is as follows:-

IF {customer.BillCountry} = "CAN"
THEN

(if (remainder(({Pbalance}/.63),1) >= .001 and remainder(({Pbalance}/.63),1)<=.250)
then (truncate(({Pbalance}/.63),1) + .25)
ELSE
if (remainder(({Pbalance}/.63),1) >.250 and remainder(({Pbalance}/.63),1)<=.50)
then (truncate(({Pbalance}/.63),1) + .50)

else
iif (remainder(({Pbalance}/.63),1) >.500 and remainder(({Pbalance}/.63),1)<=.750)
then (truncate(({Pbalance}/.63),1) + .75)

else
if (remainder(({Pbalance}/.63),1) >.750 and remainder(({Pbalance}/.63),1)<=.999999999)
then (truncate(({Pbalance}/.63),1) + 1.0)
else
if (remainder(({Pbalance}/.63),1) >=-.001 and remainder(({Pbalance}/.63),1)<=-.250)
then (truncate(({Pbalance}/.63),1) - .25)

else
if (remainder(({Pbalance}/.63),1) >-.250 and remainder(({Pbalance}/.63),1)<= -.50)
then (truncate(({Pbalance}/.63),1) - .50)

ELse
if (remainder(({Pbalance}/.63),1) >-.50 and remainder(({Pbalance}/.63),1)<= -.750)
then (truncate(({Pbalance}/.63),1) - .75)

Else
if (remainder(({Pbalance}/.63),1) > -.750 and remainder(({Pbalance}/.63),1)<= -.99999999999999)
then (truncate(({Pbalance}/.63),1) - 1.0))


This is a very important report for me.
Thanks for your help. Your early response is highly appreciated.

Judy

 

[kenhamady]

Your greater and less than signs have to be flipped for negative numbers:

if (remainder(({Pbalance}/.63),1) < -.250 and remainder(({Pbalance}/.63),1)>= -.50)
then (truncate(({Pbalance}/.63),1) - .50)

-0.5 is less than -0.25 Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[judyf60]

Thanks Ken, It works .