Conditionally assigning number of decimal places displayed

[The Grinch]

Hi All,

Is there any way to conditionally control the number of decimal places displayed by a cell based on its value? More specifically, i want zero decimal places if the value is greater than 100; 2 decimal places for values less than 100.

Decimal places doesn't appear to be configurable using the standard conditional formatting, and I’ve had no joy experimenting with the "if" function.

Any comments/help appreciated.

Thanks
The Grinch

[jasoncw]

Hi, Grinch.

Is VBA a viable solution for you?

Jason

[The Grinch]

Yes, i'd be happy to run a macro to affect the changes. Unfortunately i dont know how to write/record one that would provide the behaviour described above...

Thanks
The Grinch

[PeterB]

First of all make sure that the cells you wish to conditionally format are formatted as "General"; NOT as "Number" with two decimal places.

Next use the following modification to your equation:

IF(YourEquation > 100, Round(YourEquation,0), Round(YourEquation,2))

The above modification will perform normal rounding so 1000.6 will come out as 1001.

If you want to just remove the fractional part of the number, then use :

IF(YourEquation > 100, RoundDown(YourEquation,0), RoundDown(YourEquation,2))

[The Grinch]

Thanks Peter, The problem is i want to also format the cell as "currency", this forces you to choose a decimal place setting which will over-write the effect of your "if" nested "round" functions.

Actually, I supposed i could leave it as a general formatting and just concatenate in a pound sign...

CHEERS
The Grinch

[Carim]

Hi,

Use the following event macro ...

Please Login or Register  to view this content.