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.

"Ted" wrote in message
...
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.

Going to ask Wizard of Oz for contents of C3, D3...

Bruno

If I understand correctly, try
=IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<0),IF(ROUND(7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),""))
The formula you posted just displays the contents of C3 if all conditions
are met,
Regards,
Alan.
"Ted" wrote in message
...
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.

that worked grate Alan, thanks $;-D

I was helped with the formula earlier but couldnt see what was going wrong -
much appriciated.

Ted.


"Alan" wrote:

If I understand correctly, try
=IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<0),IF(ROUND(7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),""))
The formula you posted just displays the contents of C3 if all conditions
are met,
Regards,
Alan.
"Ted" wrote in message
...
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.

Alan, I have just noticed that it returns the ERROR message if there is no
data in the C3,D3 (etc) cells that the sums are based on - part of the
formula is to allow further calculations to not be effected by zeros appering
in cells and stuff like that.


something that may be affecting is the C3-D3 cells contain dates - he
subtraction is one date from another. Any ideas please??


"Alan" wrote:

If I understand correctly, try
=IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<0),IF(ROUND(7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),""))
The formula you posted just displays the contents of C3 if all conditions
are met,
Regards,
Alan.
"Ted" wrote in message
...
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.

perhaps if the zero in "C3-D3<0" was altered to something that symbolises a
blank space (such as the "") it may cure it??

is there a symbol that means 'blank cell'?

Ted

"Alan" wrote:

If I understand correctly, try
=IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<0),IF(ROUND(7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),""))
The formula you posted just displays the contents of C3 if all conditions
are met,
Regards,
Alan.
"Ted" wrote in message
...
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.

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

"Ron Rosenfeld" wrote in message
...

[...]
=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),""),"")

Or, a couple of byte shorter:

=IF(NOT(ISERROR(R3*S3))*(R3<S3)*(NOT(ISERROR(ROUN D(7/(R3-S3),5)))),ROUND(7/(R3-S3),5),"")

Bruno

On Mon, 21 Nov 2005 16:23:01 +0100, "Bruno Campanini"
wrote:

"Ron Rosenfeld" wrote in message
.. .

[...]
=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),""),"")

Or, a couple of byte shorter:

=IF(NOT(ISERROR(R3*S3))*(R3<S3)*(NOT(ISERROR(ROU ND(7/(R3-S3),5)))),ROUND(7/(R3-S3),5),"")

Bruno


Actually, as is, your formula has more functions than mine.

In addition, it will return values less than or equal to 0.5, so you need
another IF statement.


--ron

Hi Ron, Bruno, and others(?) I am now using the formula:

=IF(AND(C3-D30,COUNT(C3:D3)=2),IF(ROUND(7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),""),"")

If either of you spot a problem with that selection, please let me know??

It appears to be working ok, but welcome suggestions?

Many thanks, Ted.



"Ron Rosenfeld" wrote:

On Mon, 21 Nov 2005 16:23:01 +0100, "Bruno Campanini"
wrote:

"Ron Rosenfeld" wrote in message
.. .

[...]
=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),""),"")

Or, a couple of byte shorter:

=IF(NOT(ISERROR(R3*S3))*(R3<S3)*(NOT(ISERROR(ROU ND(7/(R3-S3),5)))),ROUND(7/(R3-S3),5),"")

Bruno


Actually, as is, your formula has more functions than mine.

In addition, it will return values less than or equal to 0.5, so you need
another IF statement.


--ron

"Ron Rosenfeld" wrote in message
...

Actually, as is, your formula has more functions than mine.

In addition, it will return values less than or equal to 0.5, so you need
another IF statement.


--ron

Hi Ron,

=IF(ISERROR(ROUND(7/(C3-D3),5)*C3*D3),"",
IF((ROUND(7/(C3-D3),5)0.5),ROUND(7/(C3-D3),5),""))

definitely I was unable to miss 2nd IF clause.

Ciao
Bruno

where does the additional IF need to go!??

also, how can I get sums to perform as normal, even when it is confronted by
empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and there
is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error
message because of no data etc. How can I get around this please, and have
the answer cell display a zero for the result, when there is no data to
compute

E.G.

<< using the fake data of 5-6=1 and 0-3=#VALUE!

from:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = #VALUE!

to:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = 0

I cant have zeros appear in the dependant cells because it alters other
formula, so just neet it to class a blank/empty cell as a zero.

Any ideas please??

Ted.

"Bruno Campanini" wrote:

"Ron Rosenfeld" wrote in message
...

Actually, as is, your formula has more functions than mine.

In addition, it will return values less than or equal to 0.5, so you need
another IF statement.


--ron

Hi Ron,

=IF(ISERROR(ROUND(7/(C3-D3),5)*C3*D3),"",
IF((ROUND(7/(C3-D3),5)0.5),ROUND(7/(C3-D3),5),""))

definitely I was unable to miss 2nd IF clause.

Ciao
Bruno

On Mon, 21 Nov 2005 09:17:23 -0800, Ted wrote:

Hi Ron, Bruno, and others(?) I am now using the formula:

=IF(AND(C3-D30,COUNT(C3:D3)=2),IF(ROUND(7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),""),"")

If either of you spot a problem with that selection, please let me know??

It appears to be working ok, but welcome suggestions?

Many thanks, Ted.

Your formula will give a #VALUE error if there happens to be text in C3 or D3.
If this is not desirable behavior, then change:

C3-D30

to

C3=D3


--ron

"Ted" wrote in message
...

[...]
from:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = #VALUE!

to:
A1[5] - B1[6] = C1[1]
A2[empty cell] - B2[3] = 0

=IF(ISERROR(R3*S3),0,R3+S3)
=IF(ISERROR(R3*S3),0,R3-S3)
=IF(ISERROR(R3*S3),0,R3*S3)
=IF(ISERROR(R3*S3),0,R3/S3)

In the 4th formula S3 should also be checked for
value #0.

Bruno