Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditional formatting producing inconsistant results?
Hello,
I am attempting to format a cell and the results are inconsistant. The formula looks like this: =IF(MOD(C3,0.0005)0,TRUE(),FALSE()) The results I get are inconstant. If the value of C3 is .2810 it treats it as a true statement, which is not the case. However, most other values are correctly analyzed. I have discovered the reason which is an error that shows up in about the 17th decimal place. I corrected the problem by changeing the formula to look like this: =IF(ROUND(MOD(C3,0.0005),4)0,TRUE(),FALSE()) Why the error, and is there a way to avoid this? The odd thing is that some of the values which were analyzed correctly also had error, but it was in the 18th decimal place. Anyway, I've resolved the problem for now, just wondering where else the error is going to cause problems. Craig S. FDI, Inc. |
#2
|
|||
|
|||
Craig-
I've noticed this type of error in the past, too, and I'm curious to see the responses that come from the Excel MVPs. In the meantime, can I suggest a minor change to your formula: the way it's currently set, it rounds the modulus to 4 decimal places, and I think your intent was to round C3 to 4 decimal places: =IF(MOD(ROUND(C3,4),0.0005)0,TRUE,FALSE) |
#3
|
|||
|
|||
In excel the Number precision is only 15 digits
See the "Excel specifications and limits" help file. "Craig Sink" wrote: Hello, I am attempting to format a cell and the results are inconsistant. The formula looks like this: =IF(MOD(C3,0.0005)0,TRUE(),FALSE()) The results I get are inconstant. If the value of C3 is .2810 it treats it as a true statement, which is not the case. However, most other values are correctly analyzed. I have discovered the reason which is an error that shows up in about the 17th decimal place. I corrected the problem by changeing the formula to look like this: =IF(ROUND(MOD(C3,0.0005),4)0,TRUE(),FALSE()) Why the error, and is there a way to avoid this? The odd thing is that some of the values which were analyzed correctly also had error, but it was in the 18th decimal place. Anyway, I've resolved the problem for now, just wondering where else the error is going to cause problems. Craig S. FDI, Inc. |
#4
|
|||
|
|||
Craig, I'm not sure why you say Excel is inconsistant in its results. The MOD of your example IS greater than zero (0.0000000000000000216840434497101). ANY value of which .0005 is not a factor WILL have a MOD0. So, to answer your question, this is not an error. To avoid this outcome and if you only want accuracy to 4 decimal places, try your formula as such: Code: -------------------- =IF(MOD(C3,0.0005)0.0001,TRUE(),FALSE()) -------------------- or similar (using ROUND as you did accomplishes the same thing). HTH Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=396109 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |