Hiding certain decimal values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

neil40@btinternet.com - 11 Jun 2007 17:09 GMT

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

Reply to this Message

xllea - 11 Jun 2007 17:44 GMT

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)

> Hi
>
[quoted text clipped - 22 lines]
> Thanks
> Neil

Reply to this Message

Rick Rothstein (MVP - VB) - 11 Jun 2007 18:05 GMT

> Is there a way to not show decimal figures depending on what that
> decimal is?
[quoted text clipped - 17 lines]
> 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

Reply to this Message

neil40@btinternet.com - 11 Jun 2007 21:35 GMT

On 11 Jun, 18:05, "Rick Rothstein \(MVP - VB\)"
<rickNOSPAMn...@NOSPAMcomcast.net> wrote:

> > Is there a way to not show decimal figures depending on what that
> > decimal is?
[quoted text clipped - 26 lines]
>
> - 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 to this Message

Ron Rosenfeld - 11 Jun 2007 18:25 GMT

>Hi
>
[quoted text clipped - 22 lines]
>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

Reply to this Message