Rounding Issue

[WIREMESH]

I have an application for an insurance company and I have ran into a rounding issue.

I store the data in database column amount f(10,2)

in application multiply
(488.06 x .10) = 48.81

client wants 48.80

vfp is doing (488.06 x .10) = 48.806 and rounding up to 48.81

how can I get the 48.80 instead of the 48.81

 

[Olaf Doschke]

Can't be answered for the general case, as the factor .10 surely is also not constant, or is it?

If you always multiply by .10 and that simply should shift the second digit out without ever rounding up, you can take floor(amount*10)/100 instead of amount*.10

The insurance company seems to have some rules, ask them for the math they want behind it and then I can help you program that math in VFP, but it's not clear from that example alone.

Bye, Olaf.

 

[WIREMESH]

past into vfp command window

WAIT WINDOW STR(488.06 * .10, 10, 2)

you get 48.81

 

[WIREMESH]

the floor(amount*10)/100 works

 

[Olaf Doschke]

What did you want to say with your first answer, I believe you and know foxpro does math correctly, rounding up.

If you get your factor of .10 from some other table field, then floor(amount*10)/100 will not be a general solution, though, as it particularly only works for the factor .10.

Bye, Olaf.

 

[Olaf Doschke]

What did you want to say with your first answer, I believe you and know foxpro does math correctly, rounding up.

If you get your factor of .10 from some other table field, then floor(amount*10)/100 will not be a general solution, though, as it particularly only works for the factor .10.

I would still recommend you get the more generic rule by the insurance company. I know someone having work for such a company and the rule behind this wrong math could simply be always rounding in favor of the insurance company or alwyays in favor of their customers. Whatever the rule is, you better know that rule instead of that sample, you might do this correct with floor() but it is no general solution, see?

Bye, Olaf.

 

[JRB-Bldr]

vfp is doing (488.06 x .10) = 48.806 and rounding up to 48.81

Actually it is not the VFP math that is causing the Rounding Up, it is forcing the resultant value (which has more significant decimal places) 'down' into a field with only 2 decimal places -- N(10,2)

I agree that coming up with a solution that only works for one single multiplier value and can produce the one single result that you show will most likely NOT work for a more general application.

Regardless, the following is a very convoluted, Rube-Goldberg way to get what you need, but it seems to work regardless of the multiplier...

nMultiplier = .10

* --- Begin with Character field ---
cDBF = "C:\Temp\Temp.dbf"
CREATE TABLE (cDBF) FREE (Test C(20))
APPEND BLANK

* --- Populate With Transformed Value containing more decimal places ---
REPLACE Test WITH ALLTRIM(TRANSFORM((488.06 * nMultiplier),'999999.99999'))

* --- Notice value of Test Field is: '48.8060' ---
BROWSE

* --- Truncate String Down to only 2 Decimal Places ---
REPLACE ALL test WITH LEFT(test, AT('.',test)+2)

* --- Return Field To Numeric Type with Desired Decimals ---
ALTER TABLE temp ALTER test N(10,2)

* --- Examine Results ---
BROWSE

NOTE - depending on the multiplier used, you may have to change things like the TRANSFORM() 'template', etc.

However - I would seriously question the need to use this convoluted approach, but it should work to get what you want.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

JRB-Bldr,

yes, that is a more general solution.
I would go another route.

1. seperate the rule on the result rounding and the calculation
2. Do the calculation with highest precision, meaning double values in vfp, which has about 15 decimal places
3. doing the rounding on favor of somebody - or whatever exactly the rule of the insurance company in mere mortal non math language is

This is not just a math thing, this is about fundamental program design rules, seperation of concerns, to be able to make changes independant on the calculation itself and the rounding rule.

In that aspect I'd do

amount = Cast(sometable.amount as double)
factor = Cast(someothertable.factor as double) && may also simply be .1 or cast(.1 as double)
preciseresult = calculation(amount, factor)
roundedresult = Floor(preciseresult*100)/100

Of course calculation could be a function, or a method call or you write the simple calculation itself, eg amount*factor.

Then if the rule changes to rounding the other way always, you can simply use Ceiling(), and if there is a change in calculation change that. This gives more control.

Bye, Olaf.

 

[WIREMESH]

thanks for the excellent responses.

 

[sdocker]

I know this is an old closed post, but I had a similar situation and this worked for me.

transform(ROUND(488.06*.1,1),'99999.99')

Sam

 

[jrfrye1]

try using the INT function like this;

mynumber=INT((488.06 * .10)/.0100)/100
?mynumber