Q: how to format cells when value is between -1 and 1

[auto.pilot]

Using XL07, my range of cells is populated with both positive and negative numbers. I'd like to format the output such that any number between -1 and 1 is displayed with one decimal place and any number outside of that range is formatted with no decimal place. ie:
1.00 or higher format as 1
-1.00 or lower, format as -1
A number between these, say -.52 would format as -.5

Using Cells>Format Cells>Number>Custom, I input [1].#,# which works for any number over 0. How would I capture the negative numbers, which may from time to time populate the same cells.

Thanks

jim

 

[Mike Blackman]

Hi,

Try this number format;

0.0;-0.0;0.0

 

[auto.pilot]

thanks, but that suggestion results in everything set to one decimal place.

Any other thoughts?

Thanks

jim

 

[shg]

Try [>=1]0;[<=-1]-0;0.0

 

[auto.pilot]

That's it!

Thanks

jim

 

[auto.pilot]

Thanks for the earlier suggestions. Testing my formulas over a wider range of data, I find that when numbers exceed 999, they are missing a necessary comma. ie: 1000 or 10000 or 100000 should format at 1,000, 10,000 and 100,000 etc.

What would I need to change within Format Cells > Custom to account for this?

Thanks

jim