Decimal

In a laboratory  test we do the temperature has to be reported to the
nearest 0.5�C.
Can anyone let me know how to format it so that when the result is a whole
number, it leaves the decimal point out?

eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)

Alan


0
alandrob (23)
1/9/2004 8:59:25 AM
excel.misc 78881 articles. 5 followers. Follow

18 Replies
808 Views

Similar Articles

[PageSpeed] 25

"Alan Cocks" <alandrob@netspace.net.au> wrote in message
news:btlqhd$19fi$1@otis.netspace.net.au...
> In a laboratory  test we do the temperature has to be reported to the
> nearest 0.5�C.
> Can anyone let me know how to format it so that when the result is a whole
> number, it leaves the decimal point out?
>
> eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)
>
> Alan

You cannot do this in Excel.


0
Paul
1/9/2004 9:20:02 AM
With your data in A1, in any other cell put the following formula:-

=IF(A1-INT(A1)=0,TEXT(A1,"#,##0�C"),TEXT(A1,"#,##0.00�C"))

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Alan Cocks" <alandrob@netspace.net.au> wrote in message
news:btlqhd$19fi$1@otis.netspace.net.au...
> In a laboratory  test we do the temperature has to be reported to the
> nearest 0.5�C.
> Can anyone let me know how to format it so that when the result is a whole
> number, it leaves the decimal point out?
>
> eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)
>
> Alan
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004


0
ken.wright (2489)
1/9/2004 9:52:39 AM
Or you can simply format the cell, by doing Format / Cell / Custom /
General"�C"

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Alan Cocks" <alandrob@netspace.net.au> wrote in message
news:btlqhd$19fi$1@otis.netspace.net.au...
> In a laboratory  test we do the temperature has to be reported to the
> nearest 0.5�C.
> Can anyone let me know how to format it so that when the result is a whole
> number, it leaves the decimal point out?
>
> eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)
>
> Alan
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004


0
ken.wright (2489)
1/9/2004 9:55:22 AM
Never say Never  :-)

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Paul" <none> wrote in message news:%23XwZKHp1DHA.556@TK2MSFTNGP11.phx.gbl...
> "Alan Cocks" <alandrob@netspace.net.au> wrote in message
> news:btlqhd$19fi$1@otis.netspace.net.au...
> > In a laboratory  test we do the temperature has to be reported to the
> > nearest 0.5�C.
> > Can anyone let me know how to format it so that when the result is a whole
> > number, it leaves the decimal point out?
> >
> > eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)
> >
> > Alan
>
> You cannot do this in Excel.
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004


0
ken.wright (2489)
1/9/2004 9:55:55 AM
Thanks Ken.

Why didn't I think of that!
I've been looking for a much more fancy answer.

Alan
"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:e9Wkzap1DHA.1700@TK2MSFTNGP12.phx.gbl...
> Or you can simply format the cell, by doing Format / Cell / Custom /
> General"�C"
>
> -- 
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
>
>
> "Alan Cocks" <alandrob@netspace.net.au> wrote in message
> news:btlqhd$19fi$1@otis.netspace.net.au...
> > In a laboratory  test we do the temperature has to be reported to the
> > nearest 0.5�C.
> > Can anyone let me know how to format it so that when the result is a
whole
> > number, it leaves the decimal point out?
> >
> > eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)
> >
> > Alan
> >
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004
>
>


0
alandrob (23)
1/9/2004 10:19:35 AM
OK, point taken! I read the original post as asking how to format a cell
containing the (numeric) result of a calculation. One of your replies
changes the formula so as to return text, rather than formatting the cell.
The other formats the cell for numeric entry rather than the result of a
formula. I accept that either may provide the OP with useful information.

I must admit I was surprised at the request in the context of a laboratory
report, since the decimal place is significant (even if its value is zero).
Scientifically, writing 21.0�C is different from writing 21�C as it
indicates an accuracy greater than to the nearest degree. If I received such
a report that was meant to be accurate "to the nearest 0.5�C" and found the
temperature given as 21�C, I would consider this sloppy (as it demonstrates
either a lack of scientific understanding or a typing error).

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:esFLHbp1DHA.2328@TK2MSFTNGP10.phx.gbl...
> Never say Never  :-)
>
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
>
>
> "Paul" <none> wrote in message
news:%23XwZKHp1DHA.556@TK2MSFTNGP11.phx.gbl...
> > "Alan Cocks" <alandrob@netspace.net.au> wrote in message
> > news:btlqhd$19fi$1@otis.netspace.net.au...
> > > In a laboratory  test we do the temperature has to be reported to the
> > > nearest 0.5�C.
> > > Can anyone let me know how to format it so that when the result is a
whole
> > > number, it leaves the decimal point out?
> > >
> > > eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)
> > >
> > > Alan
> >
> > You cannot do this in Excel.
> >


0
Paul
1/9/2004 10:22:43 AM
Oh don't get me wrong - I agree with you, as I HATE IT!!!!!! when I see data
that isn't consistently formatted, and I work in finance so I like to see
decimals anyway, just to be sure that I'm not lsoing anything in roundings etc.
I like things to line up nicely as well, as it just looks better IMO.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Paul" <none> wrote in message news:O6YKMqp1DHA.3436@tk2msftngp13.phx.gbl...
> OK, point taken! I read the original post as asking how to format a cell
> containing the (numeric) result of a calculation. One of your replies
> changes the formula so as to return text, rather than formatting the cell.
> The other formats the cell for numeric entry rather than the result of a
> formula. I accept that either may provide the OP with useful information.
>
> I must admit I was surprised at the request in the context of a laboratory
> report, since the decimal place is significant (even if its value is zero).
> Scientifically, writing 21.0�C is different from writing 21�C as it
> indicates an accuracy greater than to the nearest degree. If I received such
> a report that was meant to be accurate "to the nearest 0.5�C" and found the
> temperature given as 21�C, I would consider this sloppy (as it demonstrates
> either a lack of scientific understanding or a typing error).
>
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
> news:esFLHbp1DHA.2328@TK2MSFTNGP10.phx.gbl...
> > Never say Never  :-)
> >
> > --
> > Regards
> >            Ken.......................    Microsoft MVP - Excel
> >                   Sys Spec - Win XP Pro /  XL 00/02/03
> >
> > --------------------------------------------------------------------------
> --
> > It's easier to beg forgiveness than ask permission :-)
> > --------------------------------------------------------------------------
> --
> >
> >
> >
> > "Paul" <none> wrote in message
> news:%23XwZKHp1DHA.556@TK2MSFTNGP11.phx.gbl...
> > > "Alan Cocks" <alandrob@netspace.net.au> wrote in message
> > > news:btlqhd$19fi$1@otis.netspace.net.au...
> > > > In a laboratory  test we do the temperature has to be reported to the
> > > > nearest 0.5�C.
> > > > Can anyone let me know how to format it so that when the result is a
> whole
> > > > number, it leaves the decimal point out?
> > > >
> > > > eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)
> > > >
> > > > Alan
> > >
> > > You cannot do this in Excel.
> > >
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004


0
ken.wright (2489)
1/9/2004 10:50:03 AM
I have a column of temperatures.  I just wanted to ensure that it was very
obvious that they were being rounded to the nearest 0.5, and not 0.1
They are always 15� and 25�  so if the column is left justified and indented
the tens and units will roughly line up and it will be very obvious that the
only decimal being used is 0.5
Alan
"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:unbZX5p1DHA.208@TK2MSFTNGP12.phx.gbl...
> Oh don't get me wrong - I agree with you, as I HATE IT!!!!!! when I see
data
> that isn't consistently formatted, and I work in finance so I like to see
> decimals anyway, just to be sure that I'm not lsoing anything in roundings
etc.
> I like things to line up nicely as well, as it just looks better IMO.
>
> -- 
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
>
>
> "Paul" <none> wrote in message
news:O6YKMqp1DHA.3436@tk2msftngp13.phx.gbl...
> > OK, point taken! I read the original post as asking how to format a cell
> > containing the (numeric) result of a calculation. One of your replies
> > changes the formula so as to return text, rather than formatting the
cell.
> > The other formats the cell for numeric entry rather than the result of a
> > formula. I accept that either may provide the OP with useful
information.
> >
> > I must admit I was surprised at the request in the context of a
laboratory
> > report, since the decimal place is significant (even if its value is
zero).
> > Scientifically, writing 21.0�C is different from writing 21�C as it
> > indicates an accuracy greater than to the nearest degree. If I received
such
> > a report that was meant to be accurate "to the nearest 0.5�C" and found
the
> > temperature given as 21�C, I would consider this sloppy (as it
demonstrates
> > either a lack of scientific understanding or a typing error).
> >
> > "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
> > news:esFLHbp1DHA.2328@TK2MSFTNGP10.phx.gbl...
> > > Never say Never  :-)
> > >
> > > --
> > > Regards
> > >            Ken.......................    Microsoft MVP - Excel
> > >                   Sys Spec - Win XP Pro /  XL 00/02/03
> > >
> >
> --------------------------------------------------------------------------
> > --
> > > It's easier to beg forgiveness than ask permission :-)
> >
> --------------------------------------------------------------------------
> > --
> > >
> > >
> > >
> > > "Paul" <none> wrote in message
> > news:%23XwZKHp1DHA.556@TK2MSFTNGP11.phx.gbl...
> > > > "Alan Cocks" <alandrob@netspace.net.au> wrote in message
> > > > news:btlqhd$19fi$1@otis.netspace.net.au...
> > > > > In a laboratory  test we do the temperature has to be reported to
the
> > > > > nearest 0.5�C.
> > > > > Can anyone let me know how to format it so that when the result is
a
> > whole
> > > > > number, it leaves the decimal point out?
> > > > >
> > > > > eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)
> > > > >
> > > > > Alan
> > > >
> > > > You cannot do this in Excel.
> > > >
> >
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004
>
>


0
alandrob (23)
1/9/2004 11:08:48 AM
Typo!
"They are always BETWEEN 15� and 25� - - - "
"Alan Cocks" <alandrob@netspace.net.au> wrote in message
news:btm23v$15ka$1@otis.netspace.net.au...
> I have a column of temperatures.  I just wanted to ensure that it was very
> obvious that they were being rounded to the nearest 0.5, and not 0.1
> They are always 15� and 25�  so if the column is left justified and
indented
> the tens and units will roughly line up and it will be very obvious that
the
> only decimal being used is 0.5
> Alan


0
alandrob (23)
1/9/2004 11:12:07 AM
I don't think you can do it with formatting either.

But you could use an event macro:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub
    
    With Target
        If IsNumeric(.Value) Then
            If Int(.Value) = .Value Then
                .NumberFormat = "#,##0"
            Else
                .NumberFormat = "#,##0.00"
            End If
        End If
    End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
right click on the worksheet tab that should have this behavior and select view
code.

Paste that code in the code window.

Adjust the range to match your data.

Back to excel and try it out.


Alan Cocks wrote:
> 
> In a laboratory  test we do the temperature has to be reported to the
> nearest 0.5�C.
> Can anyone let me know how to format it so that when the result is a whole
> number, it leaves the decimal point out?
> 
> eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)
> 
> Alan

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/10/2004 3:43:16 PM
Hi Dave - Did you try Format / Cell / Custom / General"�C"

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Dave Peterson" <ec35720@msn.com> wrote in message
news:40001D94.FC1DCD3@msn.com...
> I don't think you can do it with formatting either.
>
> But you could use an event macro:
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>
>     If Target.Cells.Count > 1 Then Exit Sub
>     If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub
>
>     With Target
>         If IsNumeric(.Value) Then
>             If Int(.Value) = .Value Then
>                 .NumberFormat = "#,##0"
>             Else
>                 .NumberFormat = "#,##0.00"
>             End If
>         End If
>     End With
>
> End Sub
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Short course:
> right click on the worksheet tab that should have this behavior and select
view
> code.
>
> Paste that code in the code window.
>
> Adjust the range to match your data.
>
> Back to excel and try it out.
>
>
> Alan Cocks wrote:
> >
> > In a laboratory  test we do the temperature has to be reported to the
> > nearest 0.5�C.
> > Can anyone let me know how to format it so that when the result is a whole
> > number, it leaves the decimal point out?
> >
> > eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)
> >
> > Alan
>
> -- 
>
> Dave Peterson
> ec35720@msn.com


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004


0
ken.wright (2489)
1/10/2004 5:12:38 PM
No, but did you try 1.345323342?

Your format will work if the number of digits to the right of the decimal place
is always 1.  

(I saw that the OP was happy with your format, though.)

Ken Wright wrote:
> 
> Hi Dave - Did you try Format / Cell / Custom / General"�C"
> 
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
> 
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
> 
> "Dave Peterson" <ec35720@msn.com> wrote in message
> news:40001D94.FC1DCD3@msn.com...
> > I don't think you can do it with formatting either.
> >
> > But you could use an event macro:
> >
> > Option Explicit
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> >     If Target.Cells.Count > 1 Then Exit Sub
> >     If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub
> >
> >     With Target
> >         If IsNumeric(.Value) Then
> >             If Int(.Value) = .Value Then
> >                 .NumberFormat = "#,##0"
> >             Else
> >                 .NumberFormat = "#,##0.00"
> >             End If
> >         End If
> >     End With
> >
> > End Sub
> >
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > Short course:
> > right click on the worksheet tab that should have this behavior and select
> view
> > code.
> >
> > Paste that code in the code window.
> >
> > Adjust the range to match your data.
> >
> > Back to excel and try it out.
> >
> >
> > Alan Cocks wrote:
> > >
> > > In a laboratory  test we do the temperature has to be reported to the
> > > nearest 0.5�C.
> > > Can anyone let me know how to format it so that when the result is a whole
> > > number, it leaves the decimal point out?
> > >
> > > eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)
> > >
> > > Alan
> >
> > --
> >
> > Dave Peterson
> > ec35720@msn.com
> 
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/10/2004 5:34:12 PM
Even though I put 2 decimal places in the .numberformat??????

Yeah, yeah.  I was wrong.


Dave Peterson wrote:
> 
> No, but did you try 1.345323342?
> 
> Your format will work if the number of digits to the right of the decimal place
> is always 1.
> 
> (I saw that the OP was happy with your format, though.)
> 
> Ken Wright wrote:
> >
> > Hi Dave - Did you try Format / Cell / Custom / General"�C"
> >
> > --
> > Regards
> >            Ken.......................    Microsoft MVP - Excel
> >                   Sys Spec - Win XP Pro /  XL 00/02/03
> >
> > ----------------------------------------------------------------------------
> > It's easier to beg forgiveness than ask permission :-)
> > ----------------------------------------------------------------------------
> >
> > "Dave Peterson" <ec35720@msn.com> wrote in message
> > news:40001D94.FC1DCD3@msn.com...
> > > I don't think you can do it with formatting either.
> > >
> > > But you could use an event macro:
> > >
> > > Option Explicit
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > >
> > >     If Target.Cells.Count > 1 Then Exit Sub
> > >     If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub
> > >
> > >     With Target
> > >         If IsNumeric(.Value) Then
> > >             If Int(.Value) = .Value Then
> > >                 .NumberFormat = "#,##0"
> > >             Else
> > >                 .NumberFormat = "#,##0.00"
> > >             End If
> > >         End If
> > >     End With
> > >
> > > End Sub
> > >
> > > If you're new to macros, you may want to read David McRitchie's intro at:
> > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > >
> > > Short course:
> > > right click on the worksheet tab that should have this behavior and select
> > view
> > > code.
> > >
> > > Paste that code in the code window.
> > >
> > > Adjust the range to match your data.
> > >
> > > Back to excel and try it out.
> > >
> > >
> > > Alan Cocks wrote:
> > > >
> > > > In a laboratory  test we do the temperature has to be reported to the
> > > > nearest 0.5�C.
> > > > Can anyone let me know how to format it so that when the result is a whole
> > > > number, it leaves the decimal point out?
> > > >
> > > > eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)
> > > >
> > > > Alan
> > >
> > > --
> > >
> > > Dave Peterson
> > > ec35720@msn.com
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004
> 
> --
> 
> Dave Peterson
> ec35720@msn.com

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/10/2004 5:40:47 PM
LOL - You caught me too - It was just luck that his data was 1 decimal place
max, I hadn't considered that bit  :-)

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Dave Peterson" <ec35720@msn.com> wrote in message
news:4000391F.752B2330@msn.com...
> Even though I put 2 decimal places in the .numberformat??????
>
> Yeah, yeah.  I was wrong.
>
>
> Dave Peterson wrote:
> >
> > No, but did you try 1.345323342?
> >
> > Your format will work if the number of digits to the right of the decimal
place
> > is always 1.
> >
> > (I saw that the OP was happy with your format, though.)
> >
> > Ken Wright wrote:
> > >
> > > Hi Dave - Did you try Format / Cell / Custom / General"�C"
> > >
> > > --
> > > Regards
> > >            Ken.......................    Microsoft MVP - Excel
> > >                   Sys Spec - Win XP Pro /  XL 00/02/03
> > >
> >
> ----------------------------------------------------------------------------
> > > It's easier to beg forgiveness than ask permission :-)
> >
> ----------------------------------------------------------------------------
> > >
> > > "Dave Peterson" <ec35720@msn.com> wrote in message
> > > news:40001D94.FC1DCD3@msn.com...
> > > > I don't think you can do it with formatting either.
> > > >
> > > > But you could use an event macro:
> > > >
> > > > Option Explicit
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > >
> > > >     If Target.Cells.Count > 1 Then Exit Sub
> > > >     If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub
> > > >
> > > >     With Target
> > > >         If IsNumeric(.Value) Then
> > > >             If Int(.Value) = .Value Then
> > > >                 .NumberFormat = "#,##0"
> > > >             Else
> > > >                 .NumberFormat = "#,##0.00"
> > > >             End If
> > > >         End If
> > > >     End With
> > > >
> > > > End Sub
> > > >
> > > > If you're new to macros, you may want to read David McRitchie's intro
at:
> > > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > > >
> > > > Short course:
> > > > right click on the worksheet tab that should have this behavior and
select
> > > view
> > > > code.
> > > >
> > > > Paste that code in the code window.
> > > >
> > > > Adjust the range to match your data.
> > > >
> > > > Back to excel and try it out.
> > > >
> > > >
> > > > Alan Cocks wrote:
> > > > >
> > > > > In a laboratory  test we do the temperature has to be reported to the
> > > > > nearest 0.5�C.
> > > > > Can anyone let me know how to format it so that when the result is a
whole
> > > > > number, it leaves the decimal point out?
> > > > >
> > > > > eg     22�C   or   22.5�C  (not 22.�C  or 22.0�C)
> > > > >
> > > > > Alan
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > > ec35720@msn.com
> > >
> > > ---
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004
> >
> > --
> >
> > Dave Peterson
> > ec35720@msn.com
>
> -- 
>
> Dave Peterson
> ec35720@msn.com


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004


0
ken.wright (2489)
1/10/2004 5:45:41 PM
On Sat, 10 Jan 2004 11:34:12 -0600, Dave Peterson <ec35720@msn.com> wrote:

>No, but did you try 1.345323342?
>
>Your format will work if the number of digits to the right of the decimal place
>is always 1.  
>
>(I saw that the OP was happy with your format, though.)

Well, he did say that the results would be reported to the nearest 0.5�C.

If they are being entered to a higher degree of precision, one could always
round the entry to the nearest 0.5 and then apply the General format.


--ron
0
1/10/2004 7:05:42 PM
Yeah, yeah.  I was wrong.  Now you've made me say it twice in the same thread! 
<vbg>.



Ron Rosenfeld wrote:
> 
> On Sat, 10 Jan 2004 11:34:12 -0600, Dave Peterson <ec35720@msn.com> wrote:
> 
> >No, but did you try 1.345323342?
> >
> >Your format will work if the number of digits to the right of the decimal place
> >is always 1.
> >
> >(I saw that the OP was happy with your format, though.)
> 
> Well, he did say that the results would be reported to the nearest 0.5�C.
> 
> If they are being entered to a higher degree of precision, one could always
> round the entry to the nearest 0.5 and then apply the General format.
> 
> --ron

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/10/2004 7:53:01 PM
rotfl  :-)          ( But at least I snipped it so you wouldn't have to read it
again though  :-> )

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------


<snip>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004


0
ken.wright (2489)
1/10/2004 7:57:54 PM
Don't make me do it again, please!!!!

Have a good one.


Ken Wright wrote:
> 
> rotfl  :-)          ( But at least I snipped it so you wouldn't have to read it
> again though  :-> )
> 
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
> 
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
> 
> <snip>
> 
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/10/2004 8:02:42 PM
Reply:

Similar Artilces:

SOP NonInventory Unit Cost 6 Decimal Places
Is there any simple way to increase the currency decimal places to greater than the current highest value of 5? Thanx Scott, I don't believe there is any easy or even moderately difficult way to do this, as the field types used to store amounts in GP only go up to 5 decimal places. -- Victoria Yudin Dynamics GP MVP Flexible Solutions - home of GP Reports http://www.flex-solutions.com/gpreports.html blog: http://www.victoriayudin.com "Scott Rutledge" <sersoar@hotmail.com> wrote in message news:E102B81B-8555-400E-843C-240C58794294@microsoft.com... > Is there any...

Reduce currency decimals
Hi, Is there any tool which will reduce the currency decimals on our Great Plains system. We want to reduce our system currency decimals. Thank you. KT I'm not sure if this is what you mean, but there's Tools >> Utilities >> Inventory >> Change Decimal Places This utility can change the decimal places for items. I don't know of any system-wide change for decimal places. Eleni "KT" wrote: > Hi, > > Is there any tool which will reduce the currency decimals on our Great > Plains system. We want to reduce our system currency decimals....

decimal problem
Hi all I a very strange effect when using different decimal settings and calculating some values: DECLARE @Decimal1 decimal(5, 2) DECLARE @Decimal2 decimal(30, 2) SET @Decimal1 = 0.2 SET @Decimal2 = 0.2 SELECT (CONVERT(decimal(15, 15), 0.000000000322900) * (1 - @Decimal1)) SELECT (CONVERT(decimal(15, 15), 0.000000000322900) * (1 - @Decimal2)) The first result is correct (0.00000000025832000), the second is wrong, why ??? Thanks for any comments Best regards Frank Uray Hi Frank This is what happens when you multiple these precision&scales Precision 15 + 3...

Decimal
In a laboratory test we do the temperature has to be reported to the nearest 0.5�C. Can anyone let me know how to format it so that when the result is a whole number, it leaves the decimal point out? eg 22�C or 22.5�C (not 22.�C or 22.0�C) Alan "Alan Cocks" <alandrob@netspace.net.au> wrote in message news:btlqhd$19fi$1@otis.netspace.net.au... > In a laboratory test we do the temperature has to be reported to the > nearest 0.5�C. > Can anyone let me know how to format it so that when the result is a whole > number, it leaves the decimal point out? &g...

VBA code and decimals
I have some VBA code as this: rrows=lastrow/4 if lastrow is 138 then rrows should be 34.5 but I always get 34(no decimal) How do I fix that Thanks I just did this and got 34.5 Sub lastrowdecimal() lastrow = 138 rrows = lastrow / 4 MsgBox rrows End Sub -- Don Guillett SalesAid Software dguillett1@austin.rr.com "pcor" <pcor@discussions.microsoft.com> wrote in message news:F21838BC-42AA-48A3-9B0C-D2139287A187@microsoft.com... >I have some VBA code as this: > rrows=lastrow/4 > > if lastrow is 138 then rrows should be 34.5 but I always get 34(no > decimal) >...

Currency and the number of decimal places
I have an application which has neen running for a number of years, firstly on Access2000 then XP and now on Access 2003. It uses many currency fields for invoicing , credit notes etc. Each field which is of currency type has the decimal property set to 2. Up until recently the ATB balanced fine but lately it has been a few cents out. The reason is that some amounts have been put in with 3 decimal places and some calculations (eg GST of 10% of $398.68 returning to the GST field a value of $39.868) returning 3 decimal places. I guess with the calculations, I can code to make sure only ...

Decimal number to percentage formula (newbie question)
I've got a column full of numbers like 0.5, 1.0, etc. I need them to look like 0.500%. I figured out how to format the cells so that 0.5 looks like 0.500, but when I change the cell format to percentage, it makes 0.500 into 50.000%. So I figured out how to make a formula that divides 0.500 by 100 (=R1C6/100), resulting in 0.005, which I can format to a percentage and get 0.500%. Where I'm stumped is in applying that formula to the entire column. Instead of =R1C6/100, I want my formula to say =all rowsC6/100. How do I tell it {allrows}? Thanks for putting up with a newbie Ken Benson ...

No decimals?
Hello! Is it possible to stop Money (2005) from using decimal places in the base currency? I am using a currency that uses only integers and no cents (because cents are almost worthless), and invoices amounts usually get some cents due to sales tax calculations, Is it possible to get rid of them? I find this very annoying since cents accumulate over several transactions, and end up with an slightly unreal balance... Thanks! Keiv Hi, It was possible in older version (pobably until 2001). You had to change the Windows's regional settings in the control panel before a new file creatio...

Count digits before decimals and after decimals
Dear expert, Would like to count the digits before decimals and after decimals. For example, 111975.35 should be 6 digits before decimals and 2 digits after decimals 12456.25 should be 5 and 2 2478.24 should be 4 and 2 248.37 should be 3 and 2 Using LEN does not help. Can you help please? Hi, Why doesn't LEN help. try these =LEN(INT(A1)) and for the decimal portion =IF(ISERROR(FIND(".",A1)),0,LEN(A1)-FIND(".",A1)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while...

European vs American decimals
I have transferred a chart with statistics from an American Website to my Dutch version of Excel 2003. All the statistics are stated in the American format (with a period as the decimal sign): example = 8.6 I am not able to make formulas because Excel doesn't seem to recognize the period (.) as a decimal sign. (all my equations get the answer 0) I would like to know how to change the period into a comma(the Dutch decimal sign), so that I can use the formula function. or I would like to know how to make Excel recognize the Amerian format to use the formula function. Thank you H...

Don't lose my decimals!
I have a int field and a decimal field (which however contains only numeric values) I need to divide this fields: SELECT field1/CONVERT(INT,field2) The operation is Ok, but I don't get decimal. I tried even SELECT CONVERT(FLOAT,field1/CONVERT(INT,field2)) but it's the same. How do I get decimals? And I want to get only two. Thanks. Luciano "Luciano (DOC)" <lucianodoc@luciano.doc> ha scritto nel messaggio news:4b9a83b3$0$1110$4fafbaef@reader2.news.tin.it... >I have a int field and a decimal field I mean a varchar field. L. Data typ...

decimal separator?
Hi, I'm using web quiries to obtain data from web pages. Some of the sites use ',' as a desimal separator others use '.'. I'm from Finland so I'd like to use ',' as decimal separator. Is there a way to format those U.S.-format('.') numbers to Finnish-format(',') without changing the setting for the whole progmam/operating system?? Thank you, Jimmy format the cells where you will paste as text paste in the data format the cells a general do edit=>Replace Replace what . replace with , -- Regards, Tom Ogilvy Jimmy <jj_22_jj@yahoo...

Number Format
I have a system that records attribute data. An Engineer enters a specification range for the values and an integer value for the desired number of decimal places to display. I have the following formula in my report's record source Round([Act_Value], Nz([Dec_Place],0)) This works to limit the number of decimals, but doesn't "set" the display. For example [Act_value] = 0.5 [Dec_place] = 3 The above formula will diplay 0.5. I want it to display 05.00. Is there a way to do this? Any help will be appreciated. Use the format function Format(Round([Act_Value],...

Qty Decimals
Hi Out There, Does anyone know if there is a way to set a part to 4 decimals, but to show 0 decimal places on the PO & SOP Screens and printouts? We would like to have the option of 4 decimals for Bills of Materials, but we don't want the orders and PO's to show 4.0000 because it forces the data entry to add a perios in order to make it a whole number. (We've been having issues with that) Thank you! You can do this with Modifier/VBA. If you don't find a better way and want to pursue this route let me know and I will help. ...

Degrees Minutes Decimal Minutes to Decimal Degrees.
Hi All, I have got a worksheet with cells like: E174 36.992 I need to get that into decimal degrees like E174.61653 Is there a way to do this with a forumla? I start with find, left, right functions, but I ended up parenthesis hell. Cheers -Al "Al" <bigal.nz@gmail.com> wrote: > I have got a worksheet with cells like: > E174 36.992 > I need to get that into decimal degrees like > E174.61653 One way: =LEFT(A1,FIND(" ",A1)-1) & TEXT(RIGHT(A1,LEN(A1)-FIND(" ",A1))/60,".00000") ----- original messa...

format and decimals in control text
Hi format and decimals in a text box do not seem to work as expected. I can set format to general number or leave it blank and decimals to 0 or 1, makes no difference, I get 1 decimal in some text boxes and no decimals in others. the decimals are not rounded off either, they can be .3 or .9. does anyone know why and how to deal with this problem? regards LP wrote: >format and decimals in a text box do not seem to work as expected. >I can set format to general number or leave it blank and decimals to 0 or 1, >makes no difference, I get 1 decimal in some text boxes and no decimals...

number of quantity decimals
Hello: Other than perhaps rounding issues, are there any problems with having quantity decimals for some items in inventory being 5, some being 4, and some being 3? childofthe1980s If it works for your parts and pricing, it works for GP. I frequently have some parts with 5 decimals and others with 2 -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "childofthe1980s" wrote: > Hello: > > Other than perhaps rounding issue...

Decimals
Hi , I have a pedantic client who when he exports the report to excel does not want to see two decimal places . I have set it to N0 and P0 , but alas no luck , any suggestions ? Regards Malcolm ...

Preserve decimal 0 in function bar
Hi, my problem is to show decimal in bar function when decimal is 0 If i write "=3,00+2,70" excel write "=3+2,7". How can i say excel to don't truncate 0? A workaround is to write function like this: ="3,00"+"2,70" ....but i don't like it. There is another solutions? Thanks!!!! If you mean you want to change what you see in the cell, you do that with formatting. Set the number of decimal places to 2. If you mean you want to change what is displayed in the formula bar, I don't think that is possible. On Fri, 09 Sep 2005 14:56:49 GMT, ...

Decimal alignment
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) How do I align decimals in a word document? I know it has something to do with setting tabs, but I can't seem to get it right when I tried to figure it out myself! Thanks so much. Why are you trying to "figure it out" for yourself?... That's what Help is for :-) See the topic: Set tab stops Regards |:>) Bob Jones [MVP] Office:Mac On 2/7/10 6:37 PM, in article 59bb26da.-1@webcrossing.JaKIaxP2ac0, "brielle78@officeformac.com" <brielle78@officeformac.com> wrote: > Version: ...

Two digit number with one decimal, or three digit number
I would like to have one decimal point in a number, unless the number is 100 in order to fit the columns in a table. So, three digits for whatever number I type in, and if it is two digits, to have a decimal. 95.6 45.2 80.0 100 - NOT 100.0 123 - NOT 123.0 Thanks! Custom Format: [<100]0.0;0 "Cass1930" wrote: > I would like to have one decimal point in a number, unless the number is 100 > in order to fit the columns in a table. So, three digits for whatever number > I type in, and if it is two digits, to have a decimal. > > 95.6 &g...

decimal
Hi. How do you enter 400. into Excel? The important part is to leav the decimal place after the second zero. I've done everythin imaginable but the decimal always get erased after I hit enter. Than you -- oshirot ----------------------------------------------------------------------- oshiroty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1473 View this thread: http://www.excelforum.com/showthread.php?threadid=26352 Format the cell for TEXT before typing the 400. or type a leading apostrophe, like '400. The apostrophe will not show up in the cell...

how do I stop Excel taking decimal fractions as dates?
sometimes when I enter decimal fractions Excel thinks this is a date. How do I stop this? Hi Insert a single quote before the fraction '3/4 The quote will not show up in the cell, but it will tell Excel to treat the cell as Text. Alternatively, Format>Cells>Number>Fractions 3/4 will display as 3/4 but will be stored as 0.75 and can be used in further calculations. -- Regards Roger Govier "aa" <A@aa.com> wrote in message news:efV2MhV9GHA.4376@TK2MSFTNGP03.phx.gbl... > sometimes when I enter decimal fractions Excel thinks this is a date. > How do ...

Item Decimal Qtys and Currency
We have a few inventory items that were set up with DECPLQTY and DECPLCUR set up with values exceeding 1 and 3. Is this something easily changed? We have a third party that manages our Dynamics stuff but if it's a quick and easy fix I'd consider doing it myself. Any information is greatly appreciated. Thanks! DECPLQTY is easy to change. DECPLCUR can require a little more work to change if you have price lists already set for the items. Changing the DECPLCUR will delete any price lists you have set and they'll have to be recreated. To access the functionality to change ...

decimal formatting
Is there a way to format cells so that the decimal digits only show if value other than zero? For instance, if I entered "65" in a cell, i would show up simply as 65, no decimal point showing. If I changed i to "65.5"- same cell - then the .5 would show, without having t physically change the setting. On calculators, it's called floatin decimal. I checked Help, but didn't see anything like this. Am missing something??? Thanks for any help -- amyenoc ----------------------------------------------------------------------- amyenoch's Profile: http://www.e...