Cell Formating with Conditions

[tetschlagk]

I am looking to use cell formatting as such: If the number is less than zero, then 0.0; if the number is greater than 999.99, then 999.99, otherwise pull leave the number as is. I have been able to get half way there with #,##0.0;"0.0" . This makes the negative numbers = 0.0 but still leaves anything larger than 999.99 in its original number (for example 1,572.2 should show 999.99). Any thoughts?

 

[Fluff]

For the >999.99 set the format to "999.99" (including quotes)

 

[Tetra201]

The following custom format will give you the desired appearance, but it does not make "negative numbers = 0.0":

[>=999.99]"999.99";[<0]"0.0";General

 

[tetschlagk]

For the >999.99 set the format to "999.99" (including quotes)

Not sure where the "999.9" should go in the customer cell format. "999.9";"0.0" just returns 999.9 for all positive numbers. I need anything greater than 999.9 to show 999.9. every other positive number to be its value.

 

[Fluff]

Apologies, I misread your op & thought you were using conditional formatting.
Tetra201's solution should do what you're after.

 

[tetschlagk]

The following custom format will give you the desired appearance, but it does not make "negative numbers = 0.0":

[>=999.99]"999.99";[<0]"0.0";General

This is exactly what I was looking for. Thank you

 

[Marcelo Branco]

The following custom format will give you the desired appearance, but it does not make "negative numbers = 0.0":

[>=999.99]"999.99";[<0]"0.0";General

Nice!

M.

 

[Tetra201]

@ tetschlagk: You are welcome.

@ Marcelo:

 

[Marcelo Branco]

@ Marcelo:

M.