Forum Home
Press F1
Thread ID: 37475	2003-09-08 07:31:00	Making custom number types in Excel	tango (2697)	Press F1

Post ID	Timestamp	Content	User
173908	2003-09-08 07:31:00	I have a list that looks a bit like this: (actual values) 4 4.8965 2.56 5 5.056 Now I want it to round any fractional part to two places, but if there is no fractional part, just display the single number, like this: 4 4.9 2.56 5 5.01 The best I've come up with is #,##0.## as the custom definition. The problem is it turns out like this: 4. 4.9 2.56 5. 5.01 It has a decimal point even at the end of a single number :P Can anyone help? :)	tango (2697)
173909	2003-09-08 07:37:00	Another thing, I have a graph set up, but the series name is still "Series 1". How do you change it? I can't seem to find where.	tango (2697)
173910	2003-09-08 08:22:00	Hi, change the custom format to #0.00 This is the effect... 1 = 1.00 .23 = 0.23 1.3 = 1.30 # means only put a number in if its there while 0 means put a number in regardless. To change names in the chart click on the chart and you will see a chart menu appears in the tool bar. Select Chart Options tab and change the x y names or title. You could also right click chart and select Chart Options.	parry (27)
173911	2003-09-08 08:30:00	oops sorry you wanted toi change the series name. This is straight from Excel Help... Change data series names or legend text On the worksheet Click the cell that contains the data series name you want to change. Type the new name, and then press ENTER. On the chart Click the chart, and then click Source Data on the Chart menu. On the Series tab, click the data series name you want to change. In the Name box, specify the worksheet cell you want to use as the legend text or data series name. You can also type the name you want to use. If you type a name in the Name box, the legend text or data series name is no longer linked to a worksheet cell.	parry (27)
173912	2003-09-09 05:09:00	thanks, but I want it to actually display a '1', as opposed to '1.00' what does the ';' do?	tango (2697)
173913	2003-09-09 08:08:00	Hi, my apologies I obviously didnt read your post properly. Your issue is getting rid of the decimals so you want a combination of formats. The ; appears to be a separator to have multiple formats. I'll need to think about this a bit more.	parry (27)
173914	2003-09-09 09:09:00	Okily dokely, I now have a solution. Semi-colons These indicate a separation in format that means something to Excel. the format is as follows:- Positive format ; Negative format ; Zero format ; Text format So if you wanted #.00 (2 decomal places) for postive numbers and -#.0000 (4 decimal places) for negative numbers you would do this... #0.00;-#.000 There doesnt seem to be a custom format you can make that will work. Custom formats allow conditional testing but its very basic. You really need to check whether the number is an integer or not to get rid of the decimal. All is not lost though. I have managed to get it working using VBA. Pste the following code into the module of the sheet you wish the formatting to apply (Press ALt-f11, the double click the sheet name on the left hand side then paste the code in the right hand side). Private Sub Worksheet_Change(ByVal Target As Range) 'This macro formats the cell by the following rules: - 'If the cell is an integer (whole number) then the format is "#" 'If the cell is not an integer then the format is "#,##0.##" If Target.Value * 100 Mod 100 > 0 = False Then Target.NumberFormat = "#" Else Target.NumberFormat = "#,##0.##" End If End Sub Note that this code will change the format of every cell in the sheet. If you only want it to apply to a certain range then let me know. hth	parry (27)
173915	2003-09-09 09:14:00	Damn I wish you could edit posts here. Forgot to say that this is on the change event meaning it will only effect a cell when it is changed. To change existing cells then just copy it then paste it over itself (thats considered a change) then the code will run. The code is dynamic so runs automatically when a cell is changed, but only in the sheet where you have pasted the code.	parry (27)
1