Significant digits

Microsoft Excel 2003 (full product)
February 4, 2010 at 09:17:59
Specs: Windows XP, 2000
Is there any way to get Excel to format numbers in block of cells so that there are 4 significant digits, regardless of the decimal point location?

The default is to specify the number of digits after the decimal. However, this means that the number of characters/numbers in the sell varies.

I'm trying to make sure there are only and always 4 significant digits regardless of decimal point location.

Thanks very much, Sean.


See More: Significant digits

Report •

#1
February 4, 2010 at 09:26:12
Hi,

Is this what you want,

Before

1234.56
123.45
23.45

After

1234.56
0123.45
0023.45

Use Format Cells ... - Custom and "000#.00"

Regards


Report •

#2
February 4, 2010 at 09:32:13
re: "only and always 4 significant digits"

What should 12,345.678 be displayed as?


Report •

#3
February 4, 2010 at 09:48:39
1,234.5678 should be displayed as:
1234.
(12,345.678 is a larger # than any in the data)

The other #'s mentioned, before they are:
1234.56
123.45
23.45
would be:
1234.
123.4
23.45

Its for a Fortran input file, so the result must be 5 digits, one digit always being a decimal point somewhere in the number.

I'm thinking there may be a way to copy and paste, so that the new block of numbers is stored as only 5 characters each, rather than just being displayed as such.

So I guess the problem is not to ensure 4 significant figures, rather its to ensure a number in each cell of 5 characters, 1 being the decimal point.

Thanks very much, Sean.


Report •

Related Solutions

#4
February 4, 2010 at 10:10:57
The 000#. format will work actually, it will just round anything after the decimal. Losing some data accuracy. If anyone knows a better way please post.

Thanks again, Sean.


Report •

#5
February 4, 2010 at 10:18:44
Hi,

You can make 5-character text strings:

1234.
123.4
23.45
and 2.34 
becomes: 02.34


Using this formula:
=IF(LEN(F10)>5,LEFT(F10,5),IF(LEN(F10)=5,F10,REPT("0",5-LEN(F10))&F10))
where F10 contains the original number.

but there is no rounding ...

Regards


Report •

#6
February 4, 2010 at 12:14:38
Hi,

I created a custom function that returns five characters including a "."
and it rounds the values.

I haven't tested it extensively, so if you want to use it, it may require some tweaking.

Open the Visual Basic window (Alt + f11)
Find the name of your workbook - in the form: VBAProject(MyWorkbook.xls)
Right click and select Insert and insert a standard module (not class module)
Under Modules double-click on Module1
Enter the following code:

Option Explicit

Function FiveDig(Numb As Range) As Variant
Dim dblDig As Double
Dim strDig As String
Dim intDPt As Integer

On Error GoTo ErrHnd

dblDig = Numb.Value
strDig = Format(dblDig, "#.00#")
'find position of decimal point
intDPt = InStr(1, strDig, ".")

'test for too long an integer
If intDPt > 5 Then
    FiveDig = CVErr(xlErrValue)
    Exit Function
End If

Select Case intDPt
    Case Is = 5
        FiveDig = Left(CStr(Round(dblDig, 0)), 5) & "."
    Case Is = 4
        FiveDig = Left(CStr(Round(dblDig, 1)), 5)
        If Len(FiveDig) < 5 Then
            FiveDig = String(5 - Len(FiveDig), "0") & FiveDig
        End If
    Case Is = 3
        FiveDig = Left(CStr(Round(dblDig, 2)), 5)
        If Len(FiveDig) < 5 Then
            FiveDig = String(5 - Len(FiveDig), "0") & FiveDig
        End If
    Case Is = 2
        FiveDig = Left(CStr(Round(dblDig, 3)), 5)
        If Len(FiveDig) < 5 Then
            FiveDig = String(5 - Len(FiveDig), "0") & FiveDig
        End If
    Case Is = 1
        If Int(dblDig) = 0 Then
            FiveDig = Left(CStr(Round(dblDig, 3)), 5)
            Else
            FiveDig = Left(CStr(Round(dblDig, 4)), 5)
        End If
        If Len(FiveDig) < 5 Then
            FiveDig = String(5 - Len(FiveDig), "0") & FiveDig
        End If
End Select
Exit Function

'error handler
ErrHnd:
Err.Clear
FiveDig = CVErr(xlErrNA)
End Function

These are the results - originals in column A and formulas in column B:
cell B2=FiveDig(A2)

	A	B
2 1234.56 1235.
3 123.46 123.5
4 123.22 123.2
5 23.46 23.46
6 2.34 02.34
7 0.2347 0.235
8 1.2455 1.246
9 4.4 004.4
10 123456 #VALUE!

Note that numbers with more than 4 integer digits return an error.

Regards


Report •

#7
February 4, 2010 at 12:34:24
Humar, thanks very much. Extremely helpful, Sean.

Report •

#8
February 4, 2010 at 15:04:26
Hi Sean, You're welcome!

I noticed that input numbers without a decimal point are not correctly handled, so here is a revised version of the FiveDig() function.
(The existing conversions I showed remain the same, but numbers such as 12 are converted to 0012.)

Option Explicit

Function FiveDig(Numb As Range) As Variant
Dim dblDig As Double
Dim strDig As String
Dim intDPt As Integer

On Error GoTo ErrHnd

'get base values of number as number and text string
dblDig = Numb.Value
strDig = Format(dblDig, "#.00#")

'find position of "."
intDPt = InStr(1, strDig, ".")

'test if no decimal part of number
If Int(dblDig) = dblDig Then
    intDPt = 0
    strDig = CStr(dblDig)
End If

'test for too long an integer
If intDPt > 5 Then
    FiveDig = CVErr(xlErrValue)
    Exit Function
End If

'handle conversion based on position of decimal point (intDPt)
Select Case intDPt
    Case Is = 5
        FiveDig = Left(CStr(Round(dblDig, 0)), 5) & "."
    Case Is = 4
        FiveDig = Left(CStr(Round(dblDig, 1)), 5)
        If Len(FiveDig) < 5 Then
            FiveDig = String(5 - Len(FiveDig), "0") & FiveDig
        End If
    Case Is = 3
        FiveDig = Left(CStr(Round(dblDig, 2)), 5)
        If Len(FiveDig) < 5 Then
            FiveDig = String(5 - Len(FiveDig), "0") & FiveDig
        End If
    Case Is = 2
        FiveDig = Left(CStr(Round(dblDig, 3)), 5)
        If Len(FiveDig) < 5 Then
            FiveDig = String(5 - Len(FiveDig), "0") & FiveDig
        End If
    Case Is = 1
        If Int(dblDig) = 0 Then
            FiveDig = Left(CStr(Round(dblDig, 3)), 5)
            Else
            FiveDig = Left(CStr(Round(dblDig, 4)), 5)
        End If
        If Len(FiveDig) < 5 Then
            FiveDig = String(5 - Len(FiveDig), "0") & FiveDig
        End If
    Case Is = 0
        If dblDig > 9999 Then
            FiveDig = CVErr(xlErrValue)
            Else
            FiveDig = CStr(dblDig) & "."
        End If
        If Len(FiveDig) < 5 Then
            FiveDig = String(5 - Len(FiveDig), "0") & FiveDig
        End If
End Select
Exit Function

'error handler
ErrHnd:
Err.Clear
FiveDig = CVErr(xlErrNA)
End Function

Regards


Report •

#9
February 4, 2010 at 19:52:58
Works great, thanks again, Sean.

Report •

Ask Question