Formula for what you see is what you get
Posted by Somkiat on August 13, 2001 10:10 PM
This is just a simplified question:
I have 3 numbers : 1.5 and 2.5 and 3.5
Then I apply number format to these 3 cells and get : 2 and 3 and 4
What is the formula to get the answer equal to 9 which is 2+3+4? This formula should be able to calculate sum of numbers showed in cells no matter of any format be applied in the future.
I do not need 1.5+2.5+3.5 calculating which is true value in cells.
Please do not use VBA or Precision as Display command. I knew that Sum of Round these cells will do the job already. But round formula will not work in other formats.
Thank you very much,
Somkiat
Posted by Aladin Akyurek on August 14, 2001 4:29 AM
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
================
Posted by Mark W. on August 14, 2001 6:35 AM
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.
Posted by Aladin Akyurek on August 14, 2001 9:15 AM
Equally...
Posted by Somkiat on August 14, 2001 10:52 PM
Custom format
Aladin, Thank you very much.
Is there a formula or vba to see what custom formatting is in any cell?
Posted by Aladin Akyurek on August 15, 2001 2:42 AM
Re: Custom format
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
