Bankers Rounding in Excel, VBA Round function and “Precision as displayed”

[BillGALL]

Hello,
(I have the 2010 edition)

I have tried to get an answer to this question for some time now. I understand that VBA Round is capable of doing bankers rounding in Excel spreadsheets. But, due to the floating point error for calculations in Excell - an additional step is required. Entered values and calculated values must be formated using the Excel Round function before using the VBA Round function for bankers rounding. The only other option is setting the worksheet to “Precision as displayed” and using the general format for all cells. Although I do not understand everything, this is not recomended - but, it does seem to function.
My spreadsheets are as follows: Many values entered, several calculations, then bankers rounding on a value, then using that value - more calculations & so on. Since the spreadsheets will be active & not for just my personal use, I will not be able to specify the number of decimal places (Excel Round function) in a cell with a calculation. So, is the only option “Precision as displayed”?
Thank you,
Bill

 

[CharlesChuckieCharles]

What is bankers rounding?

Have you looked at ROUND( ROUNDUP( or ROUNDDOWN(

 

[shg]

VBA's Round function does indeed round half to even (banker's rounding), but it's not available on a worksheet except via a UDF.

If you're using VBA, there is the Currency data type available, a scaled integer with a precision of 0.0001.

What's the problem that drives the topic?

 

[BillGALL]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I needed to do bankers rounding for an "active" batch record spreadsheet at work (biotech industry). So after some research, I "gathered" up some formulas in Excel and Functions in VBA to use in Excel and made a testing spreadsheet. The testing spreadsheet: in column A, I put in a value of 0.005 in A1 & in A2 I added 0.005 to A1 and so on (2000 #'s or so). In other columns I used ALL of the formulas & functions I found (including Mr Excel's) and tested them to 1 & 2 decimal places & whole #'s. All had exceptions (some, including Mr Excel's, were due to "Floating Point Error".<o></o>
<o></o>
How I understand the problem of floating point error using VBA Round in Excel spreadsheets for bankers rounding:<o></o>
1) No complicated function is necessary, just:<o></o>
I<o></o>
Public Function BRound(num, digits)<o></o>
BRound = Round(num, digits)<o></o>
End Function<o></o>
<o> </o>
II<o></o>
Now there are two choices in Excel: use the Round function for all cells with entered values and calculations or set the worksheet as Precision as displayed. <o></o>
<o> </o>
Both have error associated – the number of decimal places must be specified & therefore, the possibility of truncating significant figures in a calculation will always exist.<o></o>
<o> </o>
For the Round function, it is part of the function. For “Precision as displayed”, there are additional problems (below).<o></o>
<o></o>
2) How this problem was explained to me: Something simple like 0.005 looks "exact", and it is in the decimal system, but the binary system is built on powers of 2 and reciprocals of powers of two and 0.0005 cannot be formed by adding together various powers of 2.
The 0.005 part of 1.005 does not fit into a binary value exactly, so it gets rounded to an exact binary value, but that exact binary value is not an exact decimal value. So, the cumulative additions and equation values are not exact. <o></o>
<o> </o>
Using Excel's ROUND function: Ensures the result to a set number of decimal places that forces Excel to account for the inexactness and convert the binary value to a proper decimal value. This “forces” the entered values to be more accurate – however, using this method for a cell with calculations is the problem - the number of decimal places needs to be specified or is user defined. Therefore, the possibility of significant digits from a calculation being truncated before using VBA Round function will always exist. <o></o>
For example, if the calculated value in the cell is 100.5000000000003 – and if the cell is formatted as Round(B2,10) the value would be 100.5, using VBA Round to a whole number of the value 100.5000000000 would yield 100 – not 101. <o></o>
<o> </o>
Using the “Precision as displayed” option: As explained to me: Fixed point (precision as displayed) is only more accurate for addition and subtraction calculations. For multiplication, division, exponentiation and all other numerical calculations, fixed point provides less accuracy than floating point. So, the precision gains in addition/subtraction are offset by losses in multiplication/division/exponentiation. In Excel, floating point is more accurate for arbitrary calculations than fixed point. And, all precision is lost after four decimal places. “Precision as displayed” may fix some problems, but is just as likely to cause as many or more problems than it will correct.<o></o>
So, can bankers rounding be done in Excel without exceptions? Is there another possible Excel format to use before using VBA Round?<o></o>
Again, I am not sure that I fully understand the problem yet - help.<o></o>
Thank you,<o></o>
Bill<o></o>

 

[shg]

Just to be clear, "Excel's rounding errors" have nothing to do with Excel, and everything to do with your computer's IEEE 754 binary representation of floating point numbers.

VBA Round will consistently round numbers ending 0.5 to even, because 0.5 can be represented precisely (depending on how you arrive at it). For longer decimals ending in 5, that's not the case; their binary representations are non-zero repeating decimals. The value 0.05, for example, is stored as 3FA999999999999A.

But I fail to see why this causes a problem, other than academically. Can you provide a specific example?

Curiously, IEEE 854, and an addendum to 754, describe a base 10 floating-point representation that would eliminate this type of problem (but not the problem of limited precision), but they have been adopted at all to my knowlede.

 

[BillGALL]

Hello & thanks shg,
At this point, I am simply trying to find an answer...
1) From the previous posts - do I fully understand the problem?
2) If I choose the Excel Round function, it would be required to use the Round function for not only the entered values - but the cells with calculations as well(has it's own problems)...
3) If correct, would it would be best to describe it to my supervisor as "bankers rounding is only possible to "x" decimal places & the number of decimal places must be specified"? Again, this would be for calculated values in an "active" worksheet after all variables are entered.
Thank you,
Bill

 

[CharlesChuckieCharles]

What is the problem you are trying to solve?

 

[BillGALL]

For the "active" Excel spreadsheets I am working on, there should be no difference using a calculator, looking at the value, & doing bankers rounding. The "active" spreadsheets have entered values, some calculations & that determined value needs to be rounded by bankers rounding, then - that value is used for additional calculations & so on... <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
The problem: I should not be formatting(limiting) a number of decimals for an equation(Excel Round) & then using bankers rounding - because, if the example from my previous post occurred - the wrong answer would then be used for additional calculations... And, if "Precision as displayed" is used - there are additional problems - including "all precision is lost after 4 decimal places" (so, bankers rounding to 4 decimal places?).<o></o>
<o> </o>
So, again - is it only possible to do bankers rounding "to a specified number of decimal places for a calculated value in a cell"?<o></o>
<o> </o>
Thank you for the help - it is greatly appreciated,<o></o>
Bill<o></o>

 

[shg]

For the "active" Excel spreadsheets I am working on, there should be no difference using a calculator,

Lacking the context of an actual problem, I have no suggestion.

Good luck.

 

[BillGALL]

Ok, I need to explain this to others who may use my spreadsheets or management & not sure how to be more clear - but:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
1) Given a value in a cell produced from an equation - the cell needs to be formatted as "ROUND(cell, x). Where "x" must be user defined.<o></o>
2) Then, VBA ROUND is used for bankers rounding of that cell value in another cell.<o></o>
3) So - due to #1, bankers rounding is ONLY done to a "user defined # of decimal places".<o></o>
4) And, if "precision as displayed" is used, the calculated value in any cell is only accurate to 4 decimal places (for proper bankers rounding when comparing to a calculator). So, if a calculated value (by calculator) was 100.5000001 - then, the Excel value "may" be 100.5000 - and for VBA ROUND(cell,0) "may" round to 100, not 101. So, BOTH CASES would need to be explained to anyone using the spreadsheets as a POSSIBILITY.<o></o>
<o></o>
Unless there is another option - or, I do not fully understand. Again, thank you for the help - I am no Excel expert - just trying to understand.<o></o>
Bill <o></o>