Fundamental use of Round

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

In the calculation:
C.Offset(0, 3).Value = qty * rate

how may I ensure that calculated value is to 2 decimal places?

That is to say, if the computed value was 10.494 then it would
Round to 10.49, but if it were 10.495, then it would Round to
10.50.

Regards.
 
Just a caveat: Round() is a VBA6 introduction, so won't work with XL97
or MacXL.

Also, remember that VBA's Round uses what MS calls "Banker's Rounding"
in which numbers ending with the digit 5 are rounded to the nearest
*even* number. This is different than XL's Symmetric Arithmetic ROUND
which always rounds away from zero:


Bankers: Round(4.495, 2) ==> 4.50
Round(4.485, 2) ==> 4.48

XL's: ROUND(4.495, 2) ==> 4.50
ROUND(4.485, 2) ==> 4.49

There are dozens of ways to round numbers. See

http://support.microsoft.com/default.aspx?scid=kb;en-us;196652

for a fair discussion with VBA algorithms...
 
Many thanks.

This could be a problem?

I have '97 and '2000 Excel users working with an addin
developed under Excel2000, and we need a solution
which will always give XL's Symmetric Arithmetic ROUND.

Can I achieve this, please?

Background:
Prior to this, the users' data has almost always had qty as an Integer
and always rate as a value with 2 decimal places.
This seems to be changing, such that qty is increasingly becoming a
non-Integer value, and since the results of each individual calculation
are Summed...............

The requirement is that each calculation is 'Rounded' at source,
rather than 'Round' the summed value, if that makes sense.

Regards and thanks.
 
Take a look at the MSKB article I cited - there's a VBA5 routine for
Symmetric Arithmetic Rounding there - put that in your code and call it
instead of VBA's round.
 
Many thanks and Regards...again.

JE McGimpsey said:
Take a look at the MSKB article I cited - there's a VBA5 routine for
Symmetric Arithmetic Rounding there - put that in your code and call it
instead of VBA's round.
Click to expand...
 
Back
Top