Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Display 12.5 but not 25.0 (drop .0s)
Excel 2003.
I'm working on a workbook where the cells on Sheet 1 all have the same drop-down list (contains numbers - eg 0.5, 1, 1.5, 2, 2.5, 7.5, 10, 12.5, 20 & 25). In this sheet I'm trying to format the cells with the drop-downs to show the number as selected but what happens is that excel rounds up the .5 (12.5 becomes 13 etc). When I format the cells to Number with one decimal place it's OK for the decimals but I would like it to just show 20 (not 20.0) is there a way to do this? (I've tried formatting the cells to Text and General - these both round Up the .5 I've formated the sourse of the Drop-down list as TEXT). The TOTALS column on this sheet is fomatted to General & this displays as wanted. The TOTALS are e.g. =SUM(B4:T4). Sheet 2 of this book is layed out the same as Sheet 1 and the values entered into sheet 1 are copied to sheet 2 using the Absolute Cell Ref $C$R. This sheet gets sorted by TOTAL so this sheet is an Update sheet. I'm having the same problem with this sheet as given above. Will the Fix for Sheet 1 also fix Sheet 2 or will I need something else? As another question, how can I get Sheet 2 to NOT show 0 when cells in Sheet 1 are empty? (I've got this sheet formatterd as Number right now and each cell has ='SHEET 1'!$C$R reffering to it's corresponding cell on Sheet 1). Thanks for any assistance John |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Display 12.5 but not 25.0(drop .0’s)
Try widening the columnwidth (or use a smaller font).
But keep the format for the cell General. Originalgoth wrote: Excel 2003. I'm working on a workbook where the cells on Sheet 1 all have the same drop-down list (contains numbers - eg 0.5, 1, 1.5, 2, 2.5, 7.5, 10, 12.5, 20 & 25). In this sheet I'm trying to format the cells with the drop-downs to show the number as selected but what happens is that excel rounds up the .5 (12.5 becomes 13 etc). When I format the cells to Number with one decimal place it's OK for the decimals but I would like it to just show 20 (not 20.0) is there a way to do this? (I've tried formatting the cells to Text and General - these both round Up the .5 I've formated the sourse of the Drop-down list as TEXT). The TOTALS column on this sheet is fomatted to General & this displays as wanted. The TOTALS are e.g. =SUM(B4:T4). Sheet 2 of this book is layed out the same as Sheet 1 and the values entered into sheet 1 are copied to sheet 2 using the Absolute Cell Ref $C$R. This sheet gets sorted by TOTAL so this sheet is an Update sheet. I'm having the same problem with this sheet as given above. Will the Fix for Sheet 1 also fix Sheet 2 or will I need something else? As another question, how can I get Sheet 2 to NOT show 0 when cells in Sheet 1 are empty? (I've got this sheet formatterd as Number right now and each cell has ='SHEET 1'!$C$R reffering to it's corresponding cell on Sheet 1). Thanks for any assistance John -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Display 12.5 but not 25.0 (drop .0s)
Question 1 - Conditionally show the decimal. So long as you are not looking
to have the comma then you could just format as general. If you do need the coma then reply back. Question 2 - How to show blank instead of zero. Try a formula like this possibly. =if(A1 = "", "", A1) or a custom format #,##0;-#,##0; or Tools | Options | View - Uncheck Zero Values -- HTH... Jim Thomlinson "Originalgoth" wrote: Excel 2003. I'm working on a workbook where the cells on Sheet 1 all have the same drop-down list (contains numbers - eg 0.5, 1, 1.5, 2, 2.5, 7.5, 10, 12.5, 20 & 25). In this sheet I'm trying to format the cells with the drop-downs to show the number as selected but what happens is that excel rounds up the .5 (12.5 becomes 13 etc). When I format the cells to Number with one decimal place it's OK for the decimals but I would like it to just show 20 (not 20.0) is there a way to do this? (I've tried formatting the cells to Text and General - these both round Up the .5 I've formated the sourse of the Drop-down list as TEXT). The TOTALS column on this sheet is fomatted to General & this displays as wanted. The TOTALS are e.g. =SUM(B4:T4). Sheet 2 of this book is layed out the same as Sheet 1 and the values entered into sheet 1 are copied to sheet 2 using the Absolute Cell Ref $C$R. This sheet gets sorted by TOTAL so this sheet is an Update sheet. I'm having the same problem with this sheet as given above. Will the Fix for Sheet 1 also fix Sheet 2 or will I need something else? As another question, how can I get Sheet 2 to NOT show 0 when cells in Sheet 1 are empty? (I've got this sheet formatterd as Number right now and each cell has ='SHEET 1'!$C$R reffering to it's corresponding cell on Sheet 1). Thanks for any assistance John |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Display 12.5 but not 25.0(drop .0s)
Thanks Dave,
Yes, making the font size smaller did work when I formatted cells as General. I've also taken Jims advice & Unchecked the "Zero Values" box. I've clicked the YES for both of you. Regards John "Dave Peterson" wrote: Try widening the columnwidth (or use a smaller font). But keep the format for the cell General. Originalgoth wrote: Excel 2003. I'm working on a workbook where the cells on Sheet 1 all have the same drop-down list (contains numbers - eg 0.5, 1, 1.5, 2, 2.5, 7.5, 10, 12.5, 20 & 25). In this sheet I'm trying to format the cells with the drop-downs to show the number as selected but what happens is that excel rounds up the .5 (12.5 becomes 13 etc). When I format the cells to Number with one decimal place it's OK for the decimals but I would like it to just show 20 (not 20.0) is there a way to do this? (I've tried formatting the cells to Text and General - these both round Up the .5 I've formated the sourse of the Drop-down list as TEXT). The TOTALS column on this sheet is fomatted to General & this displays as wanted. The TOTALS are e.g. =SUM(B4:T4). Sheet 2 of this book is layed out the same as Sheet 1 and the values entered into sheet 1 are copied to sheet 2 using the Absolute Cell Ref $C$R. This sheet gets sorted by TOTAL so this sheet is an Update sheet. I'm having the same problem with this sheet as given above. Will the Fix for Sheet 1 also fix Sheet 2 or will I need something else? As another question, how can I get Sheet 2 to NOT show 0 when cells in Sheet 1 are empty? (I've got this sheet formatterd as Number right now and each cell has ='SHEET 1'!$C$R reffering to it's corresponding cell on Sheet 1). Thanks for any assistance John -- Dave Peterson . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Display 12.5 but not 25.0 (drop .0s)
Thanks Jim,
Between you & Dave Peterson you've both helped me out Greatly. I've Un-checked the Zero Values box as you said. Thanks for your help I've clicked YES to show that your post helped me. regards John "Jim Thomlinson" wrote: Question 1 - Conditionally show the decimal. So long as you are not looking to have the comma then you could just format as general. If you do need the coma then reply back. Question 2 - How to show blank instead of zero. Try a formula like this possibly. =if(A1 = "", "", A1) or a custom format #,##0;-#,##0; or Tools | Options | View - Uncheck Zero Values -- HTH... Jim Thomlinson "Originalgoth" wrote: Excel 2003. I'm working on a workbook where the cells on Sheet 1 all have the same drop-down list (contains numbers - eg 0.5, 1, 1.5, 2, 2.5, 7.5, 10, 12.5, 20 & 25). In this sheet I'm trying to format the cells with the drop-downs to show the number as selected but what happens is that excel rounds up the .5 (12.5 becomes 13 etc). When I format the cells to Number with one decimal place it's OK for the decimals but I would like it to just show 20 (not 20.0) is there a way to do this? (I've tried formatting the cells to Text and General - these both round Up the .5 I've formated the sourse of the Drop-down list as TEXT). The TOTALS column on this sheet is fomatted to General & this displays as wanted. The TOTALS are e.g. =SUM(B4:T4). Sheet 2 of this book is layed out the same as Sheet 1 and the values entered into sheet 1 are copied to sheet 2 using the Absolute Cell Ref $C$R. This sheet gets sorted by TOTAL so this sheet is an Update sheet. I'm having the same problem with this sheet as given above. Will the Fix for Sheet 1 also fix Sheet 2 or will I need something else? As another question, how can I get Sheet 2 to NOT show 0 when cells in Sheet 1 are empty? (I've got this sheet formatterd as Number right now and each cell has ='SHEET 1'!$C$R reffering to it's corresponding cell on Sheet 1). Thanks for any assistance John |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Display 12.5 but not 25.0(drop .0’s)
I didn't notice the second question.
But I'd use Jim's first suggestion: =if('SHEET 1'!A1="","",'sheet 1'!a1) You may actually want to see when real 0's are brought back--or other formulas/values are really 0. Originalgoth wrote: Thanks Dave, Yes, making the font size smaller did work when I formatted cells as General. I've also taken Jims advice & Unchecked the "Zero Values" box. I've clicked the YES for both of you. Regards John "Dave Peterson" wrote: Try widening the columnwidth (or use a smaller font). But keep the format for the cell General. Originalgoth wrote: Excel 2003. I'm working on a workbook where the cells on Sheet 1 all have the same drop-down list (contains numbers - eg 0.5, 1, 1.5, 2, 2.5, 7.5, 10, 12.5, 20 & 25). In this sheet I'm trying to format the cells with the drop-downs to show the number as selected but what happens is that excel rounds up the .5 (12.5 becomes 13 etc). When I format the cells to Number with one decimal place it's OK for the decimals but I would like it to just show 20 (not 20.0) is there a way to do this? (I've tried formatting the cells to Text and General - these both round Up the .5 I've formated the sourse of the Drop-down list as TEXT). The TOTALS column on this sheet is fomatted to General & this displays as wanted. The TOTALS are e.g. =SUM(B4:T4). Sheet 2 of this book is layed out the same as Sheet 1 and the values entered into sheet 1 are copied to sheet 2 using the Absolute Cell Ref $C$R. This sheet gets sorted by TOTAL so this sheet is an Update sheet. I'm having the same problem with this sheet as given above. Will the Fix for Sheet 1 also fix Sheet 2 or will I need something else? As another question, how can I get Sheet 2 to NOT show 0 when cells in Sheet 1 are empty? (I've got this sheet formatterd as Number right now and each cell has ='SHEET 1'!$C$R reffering to it's corresponding cell on Sheet 1). Thanks for any assistance John -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
shortcut to display drop down menu | Excel Worksheet Functions | |||
In-Cell Drop down does not display | Excel Discussion (Misc queries) | |||
how to display the drop down menu icon? | Excel Discussion (Misc queries) | |||
Drop-Downs - Display another value | Excel Discussion (Misc queries) | |||
Drop-Down List does not display choices | Excel Discussion (Misc queries) |