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?

 

[Aladin Akyurek]

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

Yes... I expect so.

If the reference is in ascending order...

MATCH(1,Reference,1)

would be very fast.

 

[Jaymond Flurrie]

Yes... I expect so.

If the reference is in ascending order...

MATCH(1,Reference,1)

would be very fast.

The problem there is that it would take quite a while to sort columns to make them ascending, but I'm really happy already with getting a function that "isn't too fancy about errors etc."

 

[energman58]

Match returns the first time it finds the value it looks for - anything after this is ignored. Using OR must evaluate every term

Unless your data is changing is there any reason why you could not pre-process your array flagging only the rows that match your criteria then when you come to use it you can zoom in on the rows of interest rather than go through the whole database each time you run a calculation?

 

[Jaymond Flurrie]

Match returns the first time it finds the value it looks for - anything after this is ignored. Using OR must evaluate every term

Unless your data is changing is there any reason why you could not pre-process your array flagging only the rows that match your criteria then when you come to use it you can zoom in on the rows of interest rather than go through the whole database each time you run a calculation?

It actually does change - by Solver.

 

[energman58]

But presumably not every cell is changing? (Solver isn't that good!) If some rows stay the same you could take advantage of Excel's internal recalculation optimization routines (originally written I believe by Mr Gates himself) if you had an intermediate calculation of the ISNUMBER(Match etc) type or whatever next to your big array.

This would only recalculate if the row was changed and would save you a ton of calculation time over doing it in a single pass which refered to all rows and would thus recaculate the entire thing from scratch every time anything changed anywhere in the sheet.

I think calculation has to be set to automatic for this to work - F9 I think recalculates everything but you could check.

 

[Jaymond Flurrie]

But presumably not every cell is changing? (Solver isn't that good!) If some rows stay the same you could take advantage of Excel's internal recalculation optimization routines (originally written I believe by Mr Gates himself) if you had an intermediate calculation of the ISNUMBER(Match etc) type or whatever next to your big array.

This would only recalculate if the row was changed and would save you a ton of calculation time over doing it in a single pass which refered to all rows and would thus recaculate the entire thing from scratch every time anything changed anywhere in the sheet.

I think calculation has to be set to automatic for this to work - F9 I think recalculates everything but you could check.

I have to check into this at some point too, thanks for the note!

 

[tusharm]

If the speed with which Excel calculates an OR function affects the overall performance of your model, then you have a bigger problem than optimizing the OR or checking if MATCH proves faster.

I'd strongly suggest you reevaluate the algorithm/model you are using. Micro-optimizations of the kind you are looking at cannot possibly provide the magnitude of improvement you will get from a more judicious model.

Also, if Solver plays a key role in your model and it is a bottleneck, look at how to ensure you use a 'linear model' only. *That* will improve performance dramatically.

It actually does change - by Solver.

 

[Jaymond Flurrie]

If the speed with which Excel calculates an OR function affects the overall performance of your model, then you have a bigger problem than optimizing the OR or checking if MATCH proves faster.

I'd strongly suggest you reevaluate the algorithm/model you are using. Micro-optimizations of the kind you are looking at cannot possibly provide the magnitude of improvement you will get from a more judicious model.

Also, if Solver plays a key role in your model and it is a bottleneck, look at how to ensure you use a 'linear model' only. *That* will improve performance dramatically.

It actually is in linear mode only. I'm not sure what the real thing making my program slow is (takes an hour to set it up), but it's not Solver. If the OR-function doesn't make it too slow, then I can just leave it as is and try to find the bottleneck from elsewhere.

 

[tusharm]

Yes, locating the bottleneck is the first step in understanding how to improve performance. And, like I mentioned, tweaking the existing model might improve performance by some percentage points whereas finding and implementing a better algorithm/model will yield an improvement of some magnitude.

It actually is in linear mode only. I'm not sure what the real thing making my program slow is (takes an hour to set it up), but it's not Solver. If the OR-function doesn't make it too slow, then I can just leave it as is and try to find the bottleneck from elsewhere.