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

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. You can see that by
clicking on a cell you entered data in and looking at the formula bar... the
original number, as typed in, will still be displayed there.

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 (check back here
later on to see if you receive different advice from the newsgroup
regulars). Okay, with that said, let us proceed...

You can use a worksheet Change event macro to physically change the typed in
value by the user. However, there is a question as to how you want that
change to take place. You can truncate off any decimal value as if they were
never typed in by the user in the first place, or you can round off the
typed in value, but then there are two ways to do rounding in a macro, so
you have to make sure you specify the right one. First off, get into the
Visual Basic editor. Click Tools/Macro/VisualBasicEditor on Excel's menu bar
(or simply key in Alt+F11). Once in there, double-click on Sheet you want to
apply this macro to in the Project Explorer window (it is the one with a
tree view of the Books and Worksheets you have in your Excel project)
located in the top, left of the Visual Basic Editor window. If you don't see
it, key in Ctrl+R to force it to appear.

Okay, after you double-click the Sheet you want to apply the macro to in the
Project Explorer window, a Code window will appear in the main area of the
Visual Basic Editor. Copy/Paste the following into the open area of that
Code window...

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". (If you want to apply this procedure to a
different column, change the 5 in the IF statement in any of the procedures
shown in this posting to the column number that you want to apply it to.)
Truncation means that even if your user types in something like 123.99999,
only 123 will be left in the cell once they either hit the Enter key or move
to another Cell. If that is not what you want, there are two **rounding**
options available to you. Here is the one to duplicate the rounding you see
when you applied the format to your cell(s) originally. Instead of the code
above, Copy/Paste this code into the Code window instead...

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

This performs what most people consider "normal" rounding (if there is a
decimal of exactly .5 after the number, then the number is **always**
rounded upward to the next highest whole number. As I said, that is what the
Excel spreadsheet does. The alternative to "normal" rounding is what VBA
uses for its built-in Round function, something known as "Banker's
Rounding". In this method, numbers ending in exactly 5, which are to be
rounded to the previous decimal position, are rounded towards the previous
even number. So, this method will round 13.5 to 14 like normal rounding does
(because the number in front of the exact 5 ending is odd), but it will
round 12.5 to 12 (because the number in front of the exact 5 ending is
even). All other roundings for the Round function are the same as for normal
rounding...the only difference between the two is when the number ends in
exactly 5 and you want to round to the previous decimal position. That last
statement means the rounding effect is not only for halves. If your number
is 123.45 and you want to round to a single decimal place, Banker's Rounding
would round the number to 12.4 whereas "normal" rounding would make it
123.5. Okay, with that explanation out of the way, IF you want to apply this
Banker's Rounding method to your entries, then use the following code
instead of either of the above procedures...

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

That's it, once one of the code procedures above is placed in your Code
window, you can exit the Visual Basic Editor and any value entered into
column 5 ("E") will be changed automatically... and that change is physical,
not cosmetic.

Rick

You are seeing the correct total, because the cell formatting affects only
the display, not the underlying stored number.

Tools/ Options/ Calculation/ Precision as Displayed
would change your result, but be careful because it doesn't apply only to
the cells (or the sheet) that you've selected.

The safer option is to use =ROUND(A1,0) then add the results of those
calculations.
--
David Biddulph

"srikanth" wrote in message
...
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

"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))

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.

I wholeheartedly agree... and I had planned to include a mention of that,
but see I forgot to include it (I guess I got carried away with all that
other stuff I wrote).


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.

Yes, you are right. I should have used code similar to this instead...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
For Each R In Target
If R.Column = 5 Then R.Value = Int(R.Value)
Next
End Sub


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))

Excellent... a far, far, far better approach than the one I posted! Thanks
for following up with that.


Rick