"Rick Rothstein \(MVP - VB\)" wrote...
I have used formating a cell using currency and making the
decimal to Zero. in the cell the numbers are being displayed in
whole numbers but the total of these numbers are not the correct
total. It is even calculating the decimals and then rounding off

The problem with formatting a cell is that it is only cosmetic;
that is, it only changes the **display** of a value to match the
format, but the original number remains as typed in behind the
scenes. . . .
....
I am newly returned to Excel after a lengthy absence from it, so
the following may or may not be the best way for you to proceed
....
You can use a worksheet Change event macro to physically change
the typed in value by the user. . . .
....

Generally unwise. Better to leave entries as-is and transform the
entered values as needed in formulas.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then Target.Value = Int(Target.Value, "0")
End Sub

The above code will **truncate** away any decimal values typed by
the user IN COLUMN 5; that is, column "E". . . .
....

Copy & paste is a killer! VBA's Int(..) function takes one and ONLY
one argument. The Int call above is a syntax error.

More precisely, Int(..) returns the nearest integer less than or equal
to the original number. For positive numbers with fractional parts,
that's truncation, e.g., Int(12.3) returns 12, but for negative
numbers it's NOT truncation, as least not the way 99 out of 100
numeric programmers would define 'truncation', e.g., Int(-99.4)
returns -100 rather than -99. Probably better to use Fix(..) rather
than Int(..). See online VBA help for details.

But code isn't necessary. To sum a column of numbers rounded to
integers, use

=SUMPRODUCT(ROUND(range,0))

To sum a column of numbers truncated to integers, use

=SUMPRODUCT(TRUNC(range))

and to sum using bankers rounding, use

=SUMPRODUCT(ROUND(range-(MOD(range*2,4)=1)/2,0))