Excel way to handle OR-function?

[Jaymond Flurrie]

I have an array of 30 numbers. I want to optimize the calculation time. The original formula I had was =MAX(0,1,0,0,0,0,0,0,0,0,0,1....) and I was wondering if I save anything by re-coding the whole thing to use =OR(0,1,0,0,0,0,0,0,0,0,0,1....)

So basically the question is, what does the OR-function do after it hits that first 1 (or "TRUE", whatever)? Does that still evaluate all 30 numbers or does it understand that the last 28 numbers are meaningless one it runs to the first 1?

 

[Scott Huish]

OR doesn't hit the first 1.

OR evaluates all elements of the array as TRUE or FALSE, if any element of the array evaluates as TRUE, the result is TRUE, else FALSE.

Since zero evaluates as false and non-zero evaluates as TRUE, any non-zero number in your range will return a TRUE value.

What are you trying to accomplish exactly?

 

[Jaymond Flurrie]

OR doesn't hit the first 1.

OR evaluates all elements of the array as TRUE or FALSE, if any element of the array evaluates as TRUE, the result is TRUE, else FALSE.

Since zero evaluates as false and non-zero evaluates as TRUE, any non-zero number in your range will return a TRUE value.

What are you trying to accomplish exactly?

A fast way to break out of needless code.

The idea of OR-function should be that if I give you 5 000 numbers and ask if any of those is 1 (or true, same thing), you wouldn't care about the last 4 999 if the first one was one. But does Excel's OR-function work that way?

I know that with my original formula using MAX-function, you can't tell after the first one that the MAX is one, since you have no chance of knowing if the last number of those 5 000 is two or seventeen or something else - you have to know the last one before you can answer to "what's the biggest number there?" so this OR-function shouldn't be any slower, but is it any faster either?

So the question is: Once OR-function runs into the first one (or true), what does it do? Evaluate the rest? Ignore the rest?

 

[Aladin Akyurek]

...
So the question is: Once OR-function runs into the first one (or true), what does it do? Evaluate the rest? Ignore the rest?

Evaluates all elements of the reference one by one. Otherwise, the function wouldn't see an error in the reference...

=OR(A1:A6)

with items:

{1;0;0;0;0;#N/A}

returns #N/A, not TRUE.

Also:

=OR(TRUE,"",FALSE,AND(1+"JAD"),7)

will yield #VALUE!, not TRUE.

Of course, the question remains: Does the OR function itself detect these errors or a different part of the calculation engine?

 

[Fazza]

What are you trying to accomplish exactly?

Like Hotpepper, I wonder what you're really wanting to do.

 

[Aladin Akyurek]

Like Hotpepper, I wonder what you're really wanting to do.

> "A fast way to break out of needless code."

 

[Fazza]

 

[Jaymond Flurrie]

Evaluates all elements of the reference one by one. Otherwise, the function wouldn't see an error in the reference...

=OR(A1:A6)

with items:

{1;0;0;0;0;#N/A}

returns #N/A, not TRUE.

Also:

=OR(TRUE,"",FALSE,AND(1+"JAD"),7)

will yield #VALUE!, not TRUE.

Of course, the question remains: Does the OR function itself detect these errors or a different part of the calculation engine?

So in other words I can't do this that simple by using just normal OR-function. In that case I think I'll just use some kind of loop in VBA in style of
for i = 1 to 30
if range(i&"1").value = 1 then
iRowMax = 1
exit for
end if
next i

of something like that.

The basic idea of this whole thing is that I have a database of 100 000 rows and 30 (later up to 8 000) columns and I need to check for each row that whether there is at least a single 1 in the row. You can imagine the amount of saved calculations in case column A has nothing but ones - we'll talk about saving like 799.9 million calculations.

 

[Aladin Akyurek]

So in other words I can't do this that simple by using just normal OR-function. In that case I think I'll just use some kind of loop in VBA in style of
for i = 1 to 30
if range(i&"1").value = 1 then
iRowMax = 1
exit for
end if
next i

of something like that.

The basic idea of this whole thing is that I have a database of 100 000 rows and 30 (later up to 8 000) columns and I need to check for each row that whether there is at least a single 1 in the row. You can imagine the amount of saved calculations in case column A has nothing but ones - we'll talk about saving like 799.9 million calculations.

Something like...

SUMIF(Reference,1)>0

MATCH(1,Reference,0)

ISNUMBER(MATCH(1,Reference,0))

LOOKUP(9.99999999999999E+307,1/Reference)

ISNUMBER(LOOKUP(9.99999999999999E+307,1/Reference))

Are these any better?

 

[Jaymond Flurrie]

Something like...

SUMIF(Reference,1)>0

MATCH(1,Reference,0)

ISNUMBER(MATCH(1,Reference,0))

LOOKUP(9.99999999999999E+307,1/Reference)

ISNUMBER(LOOKUP(9.99999999999999E+307,1/Reference))

That MATCH seems like worth of trying, does it stop as soon as it finds the first one?