I would like to format a number if <10, to show 1 decimal place, if =10, no
decimal place and with red colour for negative numbers.

e.g.
1.24 to show as 1.2 (numbers in black)
10.6 to show as 11 (numbers in black)
-2.63 to show as 2.6 (numbers should be in red to denote negative)

I am trying to avoid using functions in the cells itself.

On Mon, 5 May 2008 02:50:01 -0700, jazzii
wrote:

I would like to format a number if <10, to show 1 decimal place, if =10, no
decimal place and with red colour for negative numbers.

e.g.
1.24 to show as 1.2 (numbers in black)
10.6 to show as 11 (numbers in black)
-2.63 to show as 2.6 (numbers should be in red to denote negative)

I am trying to avoid using functions in the cells itself.


If you can limit yourself to three conditions, right click on the cell and
select Format Cells.

Number/Custom Type:

[Red][<0]0.0;[<10]0.0;0

That would give all values less than zero as one decimal with a Red color.

If you need to have numbers less than <=-10 have no decimals, and you don't
want a formula in the cell itself, you'll need to use a VBA event triggered
macro to change the formatting.
--ron

Hi, thanks for the info. I will need the forth condition as well, i.e. if
-10.1, show as 10 in Red.

However, I do not know VBA. If there is another other method, please share
thanks!!

"Ron Rosenfeld" wrote:

On Mon, 5 May 2008 02:50:01 -0700, jazzii
wrote:

I would like to format a number if <10, to show 1 decimal place, if =10, no
decimal place and with red colour for negative numbers.

e.g.
1.24 to show as 1.2 (numbers in black)
10.6 to show as 11 (numbers in black)
-2.63 to show as 2.6 (numbers should be in red to denote negative)

I am trying to avoid using functions in the cells itself.


If you can limit yourself to three conditions, right click on the cell and
select Format Cells.

Number/Custom Type:

[Red][<0]0.0;[<10]0.0;0

That would give all values less than zero as one decimal with a Red color.

If you need to have numbers less than <=-10 have no decimals, and you don't
want a formula in the cell itself, you'll need to use a VBA event triggered
macro to change the formatting.
--ron

On Mon, 5 May 2008 08:44:03 -0700, jazzii
wrote:

Hi, thanks for the info. I will need the forth condition as well, i.e. if
-10.1, show as 10 in Red.

However, I do not know VBA. If there is another other method, please share
thanks!!

You could use a formula to round numbers appropriately, and then format to RED
or not depending on if the result is ABS(10).

Do you have formulas in the cells?

If not, you will have to separate your data entry from your display.
--ron