Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ian Elliott
 
Posts: n/a
Default non nested-if way to change (0) to 0?

Thanks for any help.
I have a cell with a formula, and the cell format is number, with
parenthesis for negative numbers. So negative 50 is displayed (50). And I
have set decimal places to 0, so a negative 0.005 is displayed as (0). I
would like anything less than 1 and greater than -1 to just be a 0, not a
(0). I can do that now, with a nested if:
=IF(SHEET1!A1+SHEET2!A1<1,IF(SHEET1!A1+SHEET2!A1-1,0,SHEET1!A1+SHEET2!A1),)
But this seems an inefficient way of doing it, and looks bad, especially
since the formulas are longer, more like
SHEET1!A1+SHEET2!A1+SHEET3!A1+SHEET4!A1. Is there a more elegant way of doing
this?
Thanks again.

  #2   Report Post  
Harald Staff
 
Posts: n/a
Default

In the Tools Options menu there's a setting "Precision as displayed".
Handle it with care.

HTH. Best wishes Harald

"Ian Elliott" skrev i melding
...
Thanks for any help.
I have a cell with a formula, and the cell format is number, with
parenthesis for negative numbers. So negative 50 is displayed (50). And I
have set decimal places to 0, so a negative 0.005 is displayed as (0). I
would like anything less than 1 and greater than -1 to just be a 0, not a
(0). I can do that now, with a nested if:

=IF(SHEET1!A1+SHEET2!A1<1,IF(SHEET1!A1+SHEET2!A1-1,0,SHEET1!A1+SHEET2!A1),)
But this seems an inefficient way of doing it, and looks bad, especially
since the formulas are longer, more like
SHEET1!A1+SHEET2!A1+SHEET3!A1+SHEET4!A1. Is there a more elegant way of

doing
this?
Thanks again.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell Change Color - Need Help alani New Users to Excel 3 June 29th 05 03:50 PM
change cell shading whenever contents different from previous cell zooeyhallne Excel Discussion (Misc queries) 3 June 6th 05 09:59 PM
conditional cell shading when a change occurs zooeyhall Excel Discussion (Misc queries) 1 June 6th 05 05:14 PM
How do I link two cells as to allow me to change the value in eit. jpvlvt Excel Discussion (Misc queries) 3 January 26th 05 02:28 AM
how do i make a date change automatically if i change one before . dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 10:21 PM


All times are GMT +1. The time now is 09:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"