Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding certain decimal values
Hi
Is there a way to not show decimal figures depending on what that decimal is? For example. I will have only the following in cells Whole numbers (232, 246 etc) Numbers with one decimal place with values of only x.1 or x.5 I want to ONLY show numbers that are a) Whole numbers or b) Numbers with decimal places of x.5 IE I would only want to show 232, 232.5, 240.5, 250 but NOT 242.1 or 267.1 etc. I hope this makes sense. The x.1 decimal numbers are used for a minor calculation for ranking purposes but are not needed for display purposes, whereas the x.5 numbers are! I just wondered if there was something clever I could do with Cell/ Number formatting Thanks Neil |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding certain decimal values
Have you looked at the MRound Function? It will round to the desired
multiple. MROUND(number,multiple) Number is the value to round. Multiple is the multiple to which you want to round number. in your case I would use =mround(cell ref,.5) " wrote: Hi Is there a way to not show decimal figures depending on what that decimal is? For example. I will have only the following in cells Whole numbers (232, 246 etc) Numbers with one decimal place with values of only x.1 or x.5 I want to ONLY show numbers that are a) Whole numbers or b) Numbers with decimal places of x.5 IE I would only want to show 232, 232.5, 240.5, 250 but NOT 242.1 or 267.1 etc. I hope this makes sense. The x.1 decimal numbers are used for a minor calculation for ranking purposes but are not needed for display purposes, whereas the x.5 numbers are! I just wondered if there was something clever I could do with Cell/ Number formatting Thanks Neil |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding certain decimal values
Is there a way to not show decimal figures depending on what that
decimal is? For example. I will have only the following in cells Whole numbers (232, 246 etc) Numbers with one decimal place with values of only x.1 or x.5 I want to ONLY show numbers that are a) Whole numbers or b) Numbers with decimal places of x.5 IE I would only want to show 232, 232.5, 240.5, 250 but NOT 242.1 or 267.1 etc. I hope this makes sense. The x.1 decimal numbers are used for a minor calculation for ranking purposes but are not needed for display purposes, whereas the x.5 numbers are! I just wondered if there was something clever I could do with Cell/ Number formatting What you want to happen for decimals other than x.5 is unclear. Do you want to round them as xllea suggests or do you want to just not show them because they are meaningless calculation-wise? To help clarify that for us, what to you want the number 123.9 to display as? Rick |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding certain decimal values
On Mon, 11 Jun 2007 09:09:13 -0700, "
wrote: Hi Is there a way to not show decimal figures depending on what that decimal is? For example. I will have only the following in cells Whole numbers (232, 246 etc) Numbers with one decimal place with values of only x.1 or x.5 I want to ONLY show numbers that are a) Whole numbers or b) Numbers with decimal places of x.5 IE I would only want to show 232, 232.5, 240.5, 250 but NOT 242.1 or 267.1 etc. I hope this makes sense. The x.1 decimal numbers are used for a minor calculation for ranking purposes but are not needed for display purposes, whereas the x.5 numbers are! I just wondered if there was something clever I could do with Cell/ Number formatting Thanks Neil To "not show" those values, you can use Format/Conditional Formatting =MOD(cell_ref,0.5)<0 and format the font color to be the same as the background color (nominally, white). --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding certain decimal values
On 11 Jun, 18:05, "Rick Rothstein \(MVP - VB\)"
wrote: Is there a way to not show decimal figures depending on what that decimal is? For example. I will have only the following in cells Whole numbers (232, 246 etc) Numbers with one decimal place with values of only x.1 or x.5 I want to ONLY show numbers that are a) Whole numbers or b) Numbers with decimal places of x.5 IE I would only want to show 232, 232.5, 240.5, 250 but NOT 242.1 or 267.1 etc. I hope this makes sense. The x.1 decimal numbers are used for a minor calculation for ranking purposes but are not needed for display purposes, whereas the x.5 numbers are! I just wondered if there was something clever I could do with Cell/ Number formatting What you want to happen for decimals other than x.5 is unclear. Do you want to round them as xllea suggests or do you want to just not show them because they are meaningless calculation-wise? To help clarify that for us, what to you want the number 123.9 to display as? Rick- Hide quoted text - - Show quoted text - Rick Sorry, I thought my explanation was clear, now I re-read it, it isn't! There will be NO other values other than whole numbers, x.1 or x.5 IE no values will be x.2 x.3 x.4 x.6 x.7 x.8 or x.9 Neil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hiding zero values in a pivot | Excel Discussion (Misc queries) | |||
Hiding Zero Values in Graph | Excel Discussion (Misc queries) | |||
Hiding zero values | Excel Worksheet Functions | |||
hiding zero values in charts | Charts and Charting in Excel | |||
hiding values that havent been used yet | New Users to Excel |