Go Back   Cyber Tech Help Support Forums > Software > Applications

Notices

Reply
 
Topic Tools
  #1  
Old March 11th, 2017, 03:16 PM
keith.evans's Avatar
keith.evans keith.evans is offline
CTH Subscriber
 
Join Date: Dec 2003
O/S: Windows 10 Home
Location: Anglesey, N. Wales.
Age: 63
Posts: 605
Zero Formats

Hi,

Why is it in some circumstances I get a positive zero and others a negative zero?

This has been doing my head in for a little while now!!
Attached Images
File Type: png ExcelZeros.PNG (3.0 KB, 5 views)
Reply With Quote


  #2  
Old March 12th, 2017, 08:59 AM
smurfy's Avatar
smurfy smurfy is offline
Cyber Tech Help Moderator
 
Join Date: Sep 2000
O/S: Linux
Location: Christchurch New Zealand
Posts: 9,490
Rounding? Display format to 2 decimal places, calculation goes to many more decimal places, and at some point, it will show as less than 0?
Reply With Quote
  #3  
Old March 12th, 2017, 01:22 PM
keith.evans's Avatar
keith.evans keith.evans is offline
CTH Subscriber
 
Join Date: Dec 2003
O/S: Windows 10 Home
Location: Anglesey, N. Wales.
Age: 63
Posts: 605
Hi smurfy. What I'm trying to achieve is for the text to turn red when it is no longer in credit or 'in the black'. I have achieved this in many formatted cells but the odd 1 or 2 don't display as I thought they should.

The format (I have just remembered I had) I use to fit the bill is attached along with the errant cell and same format.

I know I can set a format to round up/down and display a minus symbol but not to achieve the red text at the same time.
Attached Images
File Type: png Format01.PNG (16.9 KB, 5 views)
File Type: png Format02.PNG (16.0 KB, 5 views)
Reply With Quote
  #4  
Old March 24th, 2017, 08:08 AM
smurfy's Avatar
smurfy smurfy is offline
Cyber Tech Help Moderator
 
Join Date: Sep 2000
O/S: Linux
Location: Christchurch New Zealand
Posts: 9,490
Keith, sorry to take so long to come back to you. What I'm saying is that the actual value in the cell is still less than zero, despite only displaying zero to two decimal places.
Your number format doesn't change the value of the cell.
Try enclosing whatever formula you have there in a round function...
=ROUND((existing formula),2)
That should convert -0.000001 to 0.00 (I think).
Reply With Quote
  #5  
Old March 24th, 2017, 12:28 PM
keith.evans's Avatar
keith.evans keith.evans is offline
CTH Subscriber
 
Join Date: Dec 2003
O/S: Windows 10 Home
Location: Anglesey, N. Wales.
Age: 63
Posts: 605
Hi smurfy. If you hadn't mentioned the negative value I wouldn't have checked, so I did and found the cell contained a negative value of 0.00000000002514 !!! How the heck does that happen when I'm only dealing in numbers that only go to 2 decimal points???

Anyway, so far, I've changed the 'Totals' cells on the relevant sheet and the =ROUND format works fine. I'll have to think about whether or not I want (or need) to change all the other values in the other sheets!! My Totals sheet is far easier on the eye now so I'll call this a win smurfy!

Diolch yn fawr iawn!
Reply With Quote
  #6  
Old August 15th, 2017, 11:32 AM
keith.evans's Avatar
keith.evans keith.evans is offline
CTH Subscriber
 
Join Date: Dec 2003
O/S: Windows 10 Home
Location: Anglesey, N. Wales.
Age: 63
Posts: 605
Just to let you know smurfy, after checking all the other values (in the relevant docs) I found use of the =ROUND formula a few more times!

Thanks.....
Reply With Quote
Reply

Bookmarks

Topic Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump




All times are GMT +1. The time now is 12:38 AM.