Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative Values resulting in error
I have a formula set as follows:
=if(c27=v19,(v19),("Error")) The formula works fine until the value becomes a negative number, then it returns as a false "Error" even though the values are still equal. How can I resolve this? -- KKD |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative Values resulting in error
I tested with -10 in both C27 and V19 and got -10 as expected...
"KKD" wrote: I have a formula set as follows: =if(c27=v19,(v19),("Error")) The formula works fine until the value becomes a negative number, then it returns as a false "Error" even though the values are still equal. How can I resolve this? -- KKD |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative Values resulting in error
Do you mean that you want 4 and -4 be be treated as equal?
If so: =if(ABS(c27)=ABS(v19), v19,"Error") OR is the problem that one or both of the numbers results from a calculation and, while they appear the same, they are different in maybe the 12 place of decimal? Then use =if(ABS(c27-v19)< 1E-12, v19,"Error") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "KKD" wrote in message ... I have a formula set as follows: =if(c27=v19,(v19),("Error")) The formula works fine until the value becomes a negative number, then it returns as a false "Error" even though the values are still equal. How can I resolve this? -- KKD |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative Values resulting in error
No I don't want absolute values, but the error may be coming from the decimal
calculation as you suggested...without using absolute values, how can I tell Excel to only look at the whole number plus 2 decimal places? I know how to make it display only this, but how do I tell it to calculate only this? -- KKD "Bernard Liengme" wrote: Do you mean that you want 4 and -4 be be treated as equal? If so: =if(ABS(c27)=ABS(v19), v19,"Error") OR is the problem that one or both of the numbers results from a calculation and, while they appear the same, they are different in maybe the 12 place of decimal? Then use =if(ABS(c27-v19)< 1E-12, v19,"Error") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "KKD" wrote in message ... I have a formula set as follows: =if(c27=v19,(v19),("Error")) The formula works fine until the value becomes a negative number, then it returns as a false "Error" even though the values are still equal. How can I resolve this? -- KKD |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative Values resulting in error
Try either of these, depending on what you want displayed:
=IF(ROUND(C27,2)=ROUND(V19,2),V19,"Error") OR =IF(ROUND(C27,2)=ROUND(V19,2),ROUND(V19,2),"Error" ) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "KKD" wrote in message ... No I don't want absolute values, but the error may be coming from the decimal calculation as you suggested...without using absolute values, how can I tell Excel to only look at the whole number plus 2 decimal places? I know how to make it display only this, but how do I tell it to calculate only this? -- KKD "Bernard Liengme" wrote: Do you mean that you want 4 and -4 be be treated as equal? If so: =if(ABS(c27)=ABS(v19), v19,"Error") OR is the problem that one or both of the numbers results from a calculation and, while they appear the same, they are different in maybe the 12 place of decimal? Then use =if(ABS(c27-v19)< 1E-12, v19,"Error") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "KKD" wrote in message ... I have a formula set as follows: =if(c27=v19,(v19),("Error")) The formula works fine until the value becomes a negative number, then it returns as a false "Error" even though the values are still equal. How can I resolve this? -- KKD |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative Values resulting in error
Use the ROUND function or set Precision as Displayed.
Note the second option affects ALL cells. Gord Dibben MS Excel MVP On Mon, 1 Jun 2009 12:06:01 -0700, KKD wrote: No I don't want absolute values, but the error may be coming from the decimal calculation as you suggested...without using absolute values, how can I tell Excel to only look at the whole number plus 2 decimal places? I know how to make it display only this, but how do I tell it to calculate only this? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative Values resulting in error
You can use the ROUND function.
=IF(ROUND(C27,2)=ROUND(V19,2),V19,"Error") or =IF(ROUND(C27,2)=ROUND(V19,2),ROUND(V19,2),"Error" ) depending on how precide you want the resultant to be. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "KKD" wrote: No I don't want absolute values, but the error may be coming from the decimal calculation as you suggested...without using absolute values, how can I tell Excel to only look at the whole number plus 2 decimal places? I know how to make it display only this, but how do I tell it to calculate only this? -- KKD "Bernard Liengme" wrote: Do you mean that you want 4 and -4 be be treated as equal? If so: =if(ABS(c27)=ABS(v19), v19,"Error") OR is the problem that one or both of the numbers results from a calculation and, while they appear the same, they are different in maybe the 12 place of decimal? Then use =if(ABS(c27-v19)< 1E-12, v19,"Error") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "KKD" wrote in message ... I have a formula set as follows: =if(c27=v19,(v19),("Error")) The formula works fine until the value becomes a negative number, then it returns as a false "Error" even though the values are still equal. How can I resolve this? -- KKD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date calculations resulting in negative numbers and averages | Excel Worksheet Functions | |||
WORKDAY resulting in #VALUE error | Excel Worksheet Functions | |||
Negative numbers in resulting formula | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) |