How to format <10, show 1 decimal....

[jazzii]

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.

 

[Ron Rosenfeld]

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

 

[jazzii]

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!!

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

 

[Ron Rosenfeld]

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