Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Cell reading Balanced problem

I am using the formula below to say if a cell is 0 then the message is
Balanced.
This has always worked ok until now, but if the cell is 0.009999 for
example, it reads Unbalanced. As this is a sheet with Number formatted to 2
decimal places I still want it to read Balanced. I have tried formatting the
cell in many different ways, even moving the cell elsewhere on the sheet, but
unable to find the solution.
I have checked all cells in the calculation chain for errors in inputting
which is giving me the rogue total, but to no avail.
Does it need a better formula than the one I have to specifically bar
numbers after 0.00 so that the cell will read Balanced? Help much
appreciated. Thanks.


IF(F46=0,"< Balanced ","< Unbalanced ")
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Cell reading Balanced problem

You need to ignore anything beyond the first two decimal digits:

=IF(INT(F46*100)=0,"< Balanced ","< Unbalanced ")

--
Gary''s Student - gsnu200836


"jimboba" wrote:

I am using the formula below to say if a cell is 0 then the message is
Balanced.
This has always worked ok until now, but if the cell is 0.009999 for
example, it reads Unbalanced. As this is a sheet with Number formatted to 2
decimal places I still want it to read Balanced. I have tried formatting the
cell in many different ways, even moving the cell elsewhere on the sheet, but
unable to find the solution.
I have checked all cells in the calculation chain for errors in inputting
which is giving me the rogue total, but to no avail.
Does it need a better formula than the one I have to specifically bar
numbers after 0.00 so that the cell will read Balanced? Help much
appreciated. Thanks.


IF(F46=0,"< Balanced ","< Unbalanced ")

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Cell reading Balanced problem

Well, if you format that to 2 places, that will be 0.01.
If that is what you want to use, you could change
=IF(F46=0,"< Balanced ","< Unbalanced ") to
=IF(ROUND(F46,2)=0,"< Balanced ","< Unbalanced ")

If you don't want to use ROUND, you might prefer ROUNDDOWN
--
David Biddulph

"jimboba" wrote in message
...
I am using the formula below to say if a cell is 0 then the message is
Balanced.
This has always worked ok until now, but if the cell is 0.009999 for
example, it reads Unbalanced. As this is a sheet with Number formatted to
2
decimal places I still want it to read Balanced. I have tried formatting
the
cell in many different ways, even moving the cell elsewhere on the sheet,
but
unable to find the solution.
I have checked all cells in the calculation chain for errors in inputting
which is giving me the rogue total, but to no avail.
Does it need a better formula than the one I have to specifically bar
numbers after 0.00 so that the cell will read Balanced? Help much
appreciated. Thanks.


IF(F46=0,"< Balanced ","< Unbalanced ")



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 175
Default Cell reading Balanced problem

David
Have you test the formula? its would still return unbalance as it give 0.01
which is < 0

Do you means =IF(ROUND(F46,0)=0,"< Balanced ","< Unbalanced ")
and Format Cells Number Number Decinal places 2

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis










"David Biddulph" wrote:

Well, if you format that to 2 places, that will be 0.01.
If that is what you want to use, you could change
=IF(F46=0,"< Balanced ","< Unbalanced ") to
=IF(ROUND(F46,2)=0,"< Balanced ","< Unbalanced ")

If you don't want to use ROUND, you might prefer ROUNDDOWN
--
David Biddulph

"jimboba" wrote in message
...
I am using the formula below to say if a cell is 0 then the message is
Balanced.
This has always worked ok until now, but if the cell is 0.009999 for
example, it reads Unbalanced. As this is a sheet with Number formatted to
2
decimal places I still want it to read Balanced. I have tried formatting
the
cell in many different ways, even moving the cell elsewhere on the sheet,
but
unable to find the solution.
I have checked all cells in the calculation chain for errors in inputting
which is giving me the rogue total, but to no avail.
Does it need a better formula than the one I have to specifically bar
numbers after 0.00 so that the cell will read Balanced? Help much
appreciated. Thanks.


IF(F46=0,"< Balanced ","< Unbalanced ")




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Cell reading Balanced problem

I have given you two suggestions. If neither of those is acceptable you may
wish to define your own upper and lower limits.
Your suggestion of =IF(ROUND(F46,0)=0,"< Balanced ","< Unbalanced ") will
give Balanced for any value between -0.5 and 0.5.
If that is what you want, then use it.
If you want to define another (arbitrary) pair of limits, try something like
=IF(AND(F46lowerlimit,F46<upperlimit)=0,"< Balanced ","< Unbalanced ")
and put your limits in cells which you can reference in the formula.
What limits do you want to set?
--
David Biddulph

"francis" wrote in message
...
David
Have you test the formula? its would still return unbalance as it give
0.01
which is < 0

Do you means =IF(ROUND(F46,0)=0,"< Balanced ","< Unbalanced ")
and Format Cells Number Number Decinal places 2

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis










"David Biddulph" wrote:

Well, if you format that to 2 places, that will be 0.01.
If that is what you want to use, you could change
=IF(F46=0,"< Balanced ","< Unbalanced ") to
=IF(ROUND(F46,2)=0,"< Balanced ","< Unbalanced ")

If you don't want to use ROUND, you might prefer ROUNDDOWN
--
David Biddulph

"jimboba" wrote in message
...
I am using the formula below to say if a cell is 0 then the message is
Balanced.
This has always worked ok until now, but if the cell is 0.009999 for
example, it reads Unbalanced. As this is a sheet with Number formatted
to
2
decimal places I still want it to read Balanced. I have tried
formatting
the
cell in many different ways, even moving the cell elsewhere on the
sheet,
but
unable to find the solution.
I have checked all cells in the calculation chain for errors in
inputting
which is giving me the rogue total, but to no avail.
Does it need a better formula than the one I have to specifically bar
numbers after 0.00 so that the cell will read Balanced? Help much
appreciated. Thanks.


IF(F46=0,"< Balanced ","< Unbalanced ")






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
Reading Only Numeric Value of Cell F. Lawrence Kulchar Excel Discussion (Misc queries) 5 April 9th 08 02:58 AM
Reading the Latest Value into a cell lee Excel Worksheet Functions 1 October 26th 06 04:15 AM
Reading a cell in VBA Ruatha New Users to Excel 3 June 11th 06 05:01 PM
Reading last cell jackh7777777 Excel Discussion (Misc queries) 2 May 26th 06 02:28 AM
Reading formulae in a different cell Albert Excel Discussion (Misc queries) 3 May 5th 06 11:42 AM


All times are GMT +1. The time now is 07:37 AM.

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

About Us

"It's about Microsoft Excel"