Decimal

  • Thread starter Thread starter Alan Cocks
  • Start date Start date
A

Alan Cocks

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 said:
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
Click to expand...

You cannot do this in Excel.
 
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"))
 
Or you can simply format the cell, by doing Format / Cell / Custom /
General"°C"
 
Thanks Ken.

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

Alan
 
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).
 
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.
 
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
 
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.
 
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.)
 
Even though I put 2 decimal places in the .numberformat??????

Yeah, yeah. I was wrong.
 
LOL - You caught me too - It was just luck that his data was 1 decimal place
max, I hadn't considered that bit :-)
 
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.)
Click to expand...

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
 
Back
Top