Help with Fix function

[Ted]

I've written a little function to remove everything after the 2nd decimal
place for prices which is as follows: -

ReturnConvertedCurrency = (fix(iSterling * session("ExchangeRate") *
100) / 100)

However, it sometimes returns incorrect values. i.e. Why does the
following: -

response.write(FormatNumber((fix(2.30 * 1 * 100) / 100) , 2))

or

response.write(FormatNumber((int(2.30 * 1 * 100) / 100) , 2))

return 2.29 ?

Any help would be greatly appreciated as I've been ripping my hair out for a
few hours trying to solve this one.

Ted.

 

[Aaron [SQL Server MVP]]

I didn't know this existed in Fix as well.
http://www.aspfaq.com/2477

Don't know why you need fix or int here though. These work fine for me:

response.write(FormatNumber((2.30 * 100)/100.0, 2))
response.write(FormatPercent((2.30)/100.0, 2))

 

[Ted]

Don't know why you need fix or int here though. These work fine for me:

response.write(FormatNumber((2.30 * 100)/100.0, 2))
response.write(FormatPercent((2.30)/100.0, 2))

The reason I am using fix() is so: -

response.write(FormatNumber((fix(2.309 * 1 * 100) / 100) , 2))

will result in 2.30

Maybe I'm goint to have to re-think my appraoch to this problem.

As it's an eCommerce site I'm putting together, I need the maths to add up
precisely on the basket screen etc. I have got some functions that will
inevitably return pesky floats (adding 17.5% tax, currency conversions etc).

What I was doing was chopping off everthing after the second decimal place
in all of my calculations using the function I mentioned. This, until now,
had worked OK and all the totals had added up. Until I got the dreaded 2.30
resulting in 2.29.

Any ideas how I should tackle this problem?

Thanks for the reply,

Ted.

 

[Aaron [SQL Server MVP]]

Is it really correct to just truncate digits instead of rounding? I would
think that, especially for an e-commerce site, you would be more interested
in being correct than using fix().

 

[Ted]

If I use currency formats then there may be descrepancies when totaling the
basket (amongst other things)

i.e. after adding VAT or coverting a currency item values might end up
like: -

£3.0090
£5.1070

which appear to the user as: -

£3.01
£5.11

but totals as: -

£8.13

Problems also occur when bankers rounding is introduced. I just want
everything to be predictable. Any suggestions for the best way to achieve
this?

Regards,

Ted.

 

[Aaron [SQL Server MVP]]

i.e. after adding VAT or coverting a currency item values might end up

like: -

£3.0090
£5.1070

which appear to the user as: -

£3.01
£5.11

but totals as: -

£8.13

Problems also occur when bankers rounding is introduced. I just want
everything to be predictable. Any suggestions for the best way to achieve
this?

Yes. Round each product to the nearest penny, instead of adding products
together at fractions of pennies (introducing the possibility of rounding
down by mistake). In the above example, you should be adding 3.01 and 5.11
and getting 8.12. Why would you carry forward 3.0090? If they only bought
one product, would you charge them 3.0090? Of course not.

A better "fix" function isn't going to fix your problem, if you'll pardon
the pun.

 

[[MSFT]]

Hi Ted,

I don't understand why you said total is 0.13.

0.0090+0.1070=0.1160
0.01+0.11=0.12

It cannot be 0.13.

Luke

 

[Nathan Sokalski]

I definitely understand that certain operations used on an ecommerce site
will give pesky floats, as you mentioned:

As it's an eCommerce site I'm putting together, I need the maths to add up
precisely on the basket screen etc. I have got some functions that will
inevitably return pesky floats (adding 17.5% tax, currency conversions etc).

But rather than change your formatting and rounding techniques, I would
suggest adding the total in whatever currency the prices were originally
given in, and then multiplying that total by the exchange rate and tax
percentages. That way, you will not need to worry about adding fractions of
a cent (or whatever currency is being used); you can simply round or
concatenate the decimal places one time for the total rather doing it for
every item. Example (my conversion & tax factors may be wrong here):

Cost Conv Tax
£15.99 * 1.76 * 17.5% = 33.06732 = 33.06
£10.50 * 1.76 * 17.5% = 21.714 = 21.71
£ 8.27 * 1.76 * 17.5% = 17.10236 = 17.10
71.88368 = 71.88 (This is of by
..01)

OR

Cost Conv Tax
£15.99
£10.50
£ 8.27
£34.76 * 1.76 * 17.5% = 71.88368 = 71.88 (This does not show any unexpected
decimals)

If only some items are taxed, you may need to add up the taxable and
non-taxable items separately, but otherwise the same. You may want to
display the operations differently on the site, and I am not experienced in
economics or finance, but I usually see tax and conversions done at the end,
which I believe makes it simpler for both the user and in the code. Just a
suggestion.