Excel hangs after Formula Evaluation

  • Thread starter Thread starter dindigul
  • Start date Start date
D

dindigul

Whenever I use the following formula, which otherwise runs ok, just near
completion, it "encounters error" and a closedown occurs. The formula being:
{=IF(OR(B1=B1:B30),IF(OR(E1=F1:F30),"A/c exists","Sorry No such A/c"),"")}
Any ideas?
 
I'm not seeing a problem here. What's the difference between your formula
and this one though?

=IF(B1=B1:B30,IF(E1=F1:F30,"A/c exists","Sorry No such A/c"),"")

Commit with CTL SHIFT ENTER
 
You're not the first person to complain about this "feature".

I think Biff or Max or one of those really smart formula people have posted a
few times about this. There suggestions boil down to save first or don't do
this.

As an aside, isn't that first =or() portion always gonna return True--B1 will
equal B1 (in B1:B30).

And instead of =or(), maybe you could replace them with =countif().

=IF(COUNTIF(B1:B30,B1)>0,IF(COUNTIF(F1:F30,E1)>0,"A/C exists",
"Sorry, No such A/C"),"")

(all one cell)

But that still has the same problem with B1:B30 and B1, but it doesn't crash
excel when you step through the evaluation.
 
Well, I was reading about {=OR(B1=B1:B30)} to check about an account from
list. I wanted to check the name as well and since that makes for two
conditions I tried to use AND, but no luck. I tried {=IF(AND(Range1=A1,
Range2=B1), "OK",:Sorry")} but that always resulted in False. Hence I tried
using twin OR statements. I would love if you could help me whether I can
use AND for the above and how?
Thanks
 
So both those conditions have to match on any row?

=sumproduct(--(a1=range1),--(b1=range2))
will return the number of matching rows.

So...

=if(sumproduct(--(a1=range1),--(b1=range2))>0,"A/C exists","Nope")

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
 
Back
Top