Hiding certain decimal values

[neil40]

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

 

[xllea]

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)

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

 

[neil40]

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