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

auto.pilot

Well-known Member
Joined
Sep 27, 2007
Messages
734
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
thanks, but that suggestion results in everything set to one decimal place.

Any other thoughts?

Thanks

jim
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top