On Sun, 20 Nov 2005 15:30:05 -0800, Ted wrote:

Hi can anyone tell me please, why this formula:

=ROUND(7/(C3-D3),5)

gives an incorrect answer, when I extend it to:

=IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<0),IF(ROUND(7/(C3-D3),5)0.5,C3,""),"")

the answer should be, 604799.85862 and is when I use the first small
formula. It becomes 36891.00001

thanks,

Ted.

Your formula, in words says:

If there are numbers in C3 and D3; and if C3-D3 is not zero, then
if (your_small_formula) is greater than 0.5
output the value in C3
else output a null string
else output a null string.


The only values that you are outputting is either the contents of C3, or a null
string.

Your long formula never outputs the value of the formula.

Try this (untested) to get the same result as your_small_formula if your tests
are passed:

=IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<0),IF(ROUND(7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),""),"")

or, slightly shorter:

=IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3<D3),IF(ROUND (7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),""),"")


--ron