CODEWITHSUNDEEP
c#excelroundingcurrency
user805788
user805788

Reputation: 11

MS Excel Currency Rounding Method

Would like to know which rounding method is applied when you use Round() function on Excel? Also is there a difference when you choose to format a column as currency with two decimal places. I need to emulate Excel's results in a C# program I'm writing.

Upvotes: 1

Views: 2037

Answers (1)

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

Caution: Excel and Excel-VBA use two different algorithms.

In Excel:

=ROUND(0.5,0) returns 1.

=ROUND(1.5,0) returns 2.

In Excel-VBA: 

Print Round(0.5,0)
 0 
Print Round(1.5,0)
 2

VBA uses banker's rounding: rounds 0.5's to the nearest even integer. Note that the same logic transfers to lower-order decimal places if you Round to more decimal places. For example, Round(0.05,1) returns zero in Excel-VBA (as opposed to 0.1 in Excel).

This is one of the features of VBA I dislike most. It being inconsistent with Excel makes it even worse.

The number format makes no difference to this issue.

Upvotes: 3

Related Questions