Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel Function returning negative 0
Hi there, I am trying to run a function here but it seems that the function will return a negative value only if the values are as follows. A: 84.90 B: 57.16 C: 27.74 Excel function: =IF((+A-B-C)=A,0,(+B+A-C)) if I am to play around with values B and C. I will be able to get a positive 0. i.e. 57.15, 27.75 or 57.17,27.73 Hope someone can help. Thanks. -- ibertram ------------------------------------------------------------------------ ibertram's Profile: http://www.excelforum.com/member.php...o&userid=28730 View this thread: http://www.excelforum.com/showthread...hreadid=484212 |
#2
|
|||
|
|||
Excel Function returning negative 0
I was never good at maths, but am I correct in thinking that IF((+A-B-C)=A is the same as IF((-B-C)=0 and the same as IF(B+C=0 and the opposite of IF(B+C which would then reduce to =IF(B+C,A+B-C,0) which should give zero only when B and C are equal and opposite, or when A+B-C totals zero. Does this help your thoughts? ibertram Wrote: Hi there, I am trying to run a function here but it seems that the function will return a negative value only if the values are as follows. A: 84.90 B: 57.16 C: 27.74 Excel function: =IF((+A-B-C)=A,0,(+B+A-C)) if I am to play around with values B and C. I will be able to get a positive 0. i.e. 57.15, 27.75 or 57.17,27.73 Hope someone can help. Thanks. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=484212 |
#3
|
|||
|
|||
Excel Function returning negative 0
(-B-C)won't equal zero unless C=(-B)
where are the numbers coming from, are there decimals |
#4
|
|||
|
|||
Excel Function returning negative 0
=(A-B-C)
(your plus sign is not needed) returns 1.06581410364015E-14. I can't figure out where you think you are getting "negative 0", but I will guess that you are probably mystified as to why this calculation is not zero. Excel (and almost all other general purpose software) does binary math. Most terminating decimal fractions are non-terminating binary fractions (just as 1/3 is a non-terminating decimal fraction) and hence must be approximated. When you do math with approximate inputs, then it should be no surprise if the outputs are only approximate. Consequently your tests should allow for these approximations, as in =IF(ROUND(A-B-C,2)=0,... (assuming that you intended your formula to compare to zero instead of A) Jerry ibertram wrote: Hi there, I am trying to run a function here but it seems that the function will return a negative value only if the values are as follows. A: 84.90 B: 57.16 C: 27.74 Excel function: =IF((+A-B-C)=A,0,(+B+A-C)) if I am to play around with values B and C. I will be able to get a positive 0. i.e. 57.15, 27.75 or 57.17,27.73 Hope someone can help. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
CHR() function not working in Excel 2003 | Excel Discussion (Misc queries) | |||
Access Module coded converted to Excel Function | Excel Discussion (Misc queries) | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) |