Formula Problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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.
Click to expand...

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.
 
that worked grate Alan, thanks $;-D

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

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??
 
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
 
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.
Click to expand...

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

Or, a couple of byte shorter:

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

Bruno
 
[...]
=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),""),"")
Click to expand...

Or, a couple of byte shorter:

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

Bruno
Click to expand...

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-D3>0,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 said:
[...]
=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),""),"")
Click to expand...

Or, a couple of byte shorter:

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

Bruno
Click to expand...

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
Click to expand...
 
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
Click to expand...

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.
 
Hi Ron, Bruno, and others(?) I am now using the formula:

=IF(AND(C3-D3>0,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.
Click to expand...

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-D3>0

to

C3=D3


--ron
 
[...]
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
Click to expand...

=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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

what means 'blank cell'?? 5
Timesheet formula problem 2
someone please shorten this formula... 6
Why doesn't this work? 8
Trying to make multiple formula cell 1
Stripe out duplicate data 5
Use array/braces **WITHIN** a formula? 3
I Need get work this formula 6

Back
Top