Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Reading Only Numeric Value of Cell | Excel Discussion (Misc queries) | |||
Reading the Latest Value into a cell | Excel Worksheet Functions | |||
Reading a cell in VBA | New Users to Excel | |||
Reading last cell | Excel Discussion (Misc queries) | |||
Reading formulae in a different cell | Excel Discussion (Misc queries) |