Somkiat,

I understand the problem as one of rounding. The totaling of the numbers, however formatted, must works. Having said that, I propose:

=SUMPRODUCT(((A1:C1)-INT(A1:C1)<0.5)*(INT(A1:C1))+(A1:C1-INT(A1:C1)>=0.5)*(INT(A1:C1)+(A1:C1-INT(A1:C1)>=0.5)))

Aladin

================

Well, since you don't want to use ROUND()...

{=SUM(TEXT({1.5,2.5,3.5},"0")+0)}

Note: This is an array formula which must be

entered using the Control+Shift+Enter key

combination. The outermost braces, {}, are

not typed by you -- they're supplied by Excel

in recognition of a properly entered array

formula.

Aladin, Thank you very much.

Is there a formula or vba to see what custom formatting is in any cell?

Is there a formula or vba to see what custom formatting is in any cell?

I take it that you're interested in cells with numeric data type. This UDF is what I've found:

Function NumberFormat(cell) As String

' Returns a string that represents

' the cell's number format

' J. Walkenbach

Application.Volatile True

NumberFormat = cell.Range("A1").NumberFormat

End Function

When you start testing it, keep in mind that you force a recalc with F9 if you change the number formatting of the cell you're testing. It would be much better if this function could be autmatically triggered whenever the formatting of the cell it is applied to changes.

By the way, the above formulas imposes an in memory formatting to the cells of the range they are applied to by means of "0". In my first formulation, I avoided this (failed to mention that). That is precisely, imho, is its charm.

Aladin