Excel - Custom Format - Show 12.5 but not 12.0 (drop .0s) ?
Asked By Originalgoth
14-Jan-10 11:11 PM
I am scratching my head and trying lots of different things here.....
I have created a Drop-down list that contains both whole numbers and numbers
with 1 decimal place (this is always .5 eg 1.5, 2.5, 7.5 & 12.5).
How can I custom format the cells with these drop-downs to show the .5
whenever it is present but NOT show the whole numbers as eg 10.0 (just show
it as 10) ?
(I have tried them as Text & General but these both round Up the 12.5 to 13 -
not what I want).
These drop-dows go towards a TOTAL at the end of each row so I cannot enter
them as text.
Oddly enough, the TOTAL column is formated as General and this displays it
as I want it to (shows xx.5 and xx).......I cannot figure that one out.
Basically what I am asking is, Is it possible to format these cells to 1
decimal place & no decimal place depending on the number chosen from the list.
If anyone could also explain to me the syntax meanings in Custom Format of #
; 0 (what do they each do or mean) ?
(It might save me from having to ask questions about it in the future).
Thanks in Advace for any help on either of these questions
John
Advace
(1)
Zeroes
(1)
Zeros
(1)
Downs
(1)
Dows
(1)
Pads
(1)
Jacob Skaria replied to Originalgoth
Format it to General to display as 12.5 as 12.5 and 12 as 12
0 is digit placeholder which pads the value with zeros to fill the format.
; is the separator used in custom formats to differentiate positive section,
negative section and zeroes as below...
+ve;-ve;0
--
Jacob
Dave Peterson replied to Originalgoth
Did you try using a General format and widening the column or reducing the font
size--like in your earlier post
--
Dave Peterson
How to stop Excel from automatically truncating leading zeros Excel Is there a setting in Excel 2007 to keep it from automatically trunkating leading zeros like when pasting in zip codes or department codes. When Excel sees what it thinks is a number, it treats it like a number and drops leading zeros. Is there a setting in Options or somewhere to tell excel to leave the leading zeros. We open lots of CSV files in EXCEL and it sees a number (such as a department code) and drops the leading zeros. In simple, does Excel have a setting to prevent this from happening as a default Discussions Microsoft Excel (1) Excel 2007 (1) Excel (1) Hypothesis (1) Hypotheses (1) Treats (1) Zeroes (1) Zeros (1) Simply, No. But there are things you can do. You can format the range as text and leading zeroes will be retained. If your numbers are a fixed length (say) 9 digits you can
Stripping leading zeros Excel Why does Excel 2007 insist upon stripping leading zeros? Here is an example: Assume this data in a column: ABC0001 ABC0002 ABC0003 Even if 0001 0002 0003 which is the desired result. If you edit individual cells, the leading zeros are preserved. But using replace, the leading zeros are removed. Excel Discussions Excel 2007 (1) Michael Bednarek (1) Format (1) POST (1) Format cells (1) Zeros (1) microsoft.public.excel: Yes, very annoying indeed. What happens if you change replacing the to: custom, 0000. When you remove ABC via replace the numbers will have 4 leading zeros. keywords: Stripping, leading, zeros description: Why does Excel 2007 insist upon stripping leading zeros Here is an example: Assume this data in a column: ABC0001 ABC0002 ABC0003 Even if
How to display zeros at the beginning of a number? Excel I have inventory numbers that start with zeros. How do I format cells to display these zeros. ex. . / . 000456789 FYI The numbers do not have the same amount of zeros in front of them. Thanks Excel Discussions Microsoft Excel (1) Larry Allen (1) Format (1 format the cells as Text but if you change the format you will lose the zeros. Gord Dibben MS Excel MVP hi Gord, no, not a text format, it is a isabelle Le 2011-04-21 20:13, Gord Dibben a ?crit : keywords: How, to, display, zeros, at, the, beginning, of, a, number? description: I have inventory numbers that start with zeros. How do I format cells to display these zeros. ex. . / . 000456789 FYI The numbers do not have the same amount
Hiding Zeros in Data Table Excel How do I hide the zeros imbedded in a chart data table. The worksheet data shows a blank cell but zeros appear in the data table in the chart? Am using excel 2007 - - Message posted via aspx / excel-chart / 201004 / 1 Excel Charting Discussions Microsoft Excel (1) Worksheet (1) ISNA (1) Zeros (1) Suppresses (1) Bliengme (1) I never use data table, so this is just what in Classic Excel. - Jon - -- -- -- Jon Peltier Peltier Technical Services, Inc. http: / / peltiertech.com / keywords: Hiding, Zeros, in, Data, Table description: How do I hide the zeros imbedded in a chart data table. The worksheet data shows a blank cell but zeros appear in the data table in the chart Am using exc
Chart and Zeros Excel This is the issue: Let say I have : ColumnA ColumnB 1 234 2 432 x-value" Column A, and "y-value" Column B. But I do not want the zeros to appear in the graph. week by week the zeros will change as days pass until reach 300. automatically. Is there any way to set the graph up to take just number greater and lesser than zeros? - - asdf Excel Charting Discussions Chart (1) Workbook (1) AVERAGE (1) Zeros (1) Asdf (1) Axis (1) Week (1) Days (1) I am assuming that your values think you would find that "" is plotted as a zero. - - David Biddulph keywords: Chart, and, Zeros description: This is the issue: Let say I have : ColumnA ColumnB 1 234 2 432