Speed of =if( Options ... and preventing #DIV/0!

[ajones]

I have been working on a fairly large and complex workbook. I have had to make several compromises regarding speed and optimizations vs extra columns and formula options etc.

Thanks to board searches, and advice from others I am getting fairly close being finished.

I then ran into a new interesting problem. I need to calculate a far number of percentages. About 6 worksheets each with a matrix of 10 x 4 of the formula of c4/$g4 (adjusted based on the cell location).

That did not slow my worksheet down much more then its current speed.

Then i realized i was getting several #DIV/0! errors. So then I created a formula of =IF($G4=0,0,C4/$G4). I replaced all my percentage calculations with that and things just slowed down.

I thought I remembered reading that if the IF statement evaluated one way most of the time it was faster to have option first so I swapped the formula to be =IF($G4<>0,C4/$G4,0).

Unfortunately that was not any faster and could have been even a little slower.


So my questions are...

1. What is are the various most efficient options to hide #DIV/0

2. Are if statements really that slow for something like this?

3. What is faster =IF($G4=0,0,C4/$G4) or =IF($G4<>0,C4/$G4,0) if most of the time G4 will NOT be zero.

Would I do better to return text like "-" or something?

thanks for the help

Alan




I tried to add various keywords to the post in case the response could help others that are searching.

 

[sunnyland]

Hie,

If you are not going to use these cells to calculate something else one option to mask the error will be to use conditional formatting:

=ISERROR(J13)

and if true give the font color the color of the background.

meaning they will not show, but the error still be there.

if there is no error the result display.

 

[ajones]

Sunnyland,

Interesting idea... i wonder how much time and speed iserror(j13) would take. This would mean the division would have had to occur then a check for iserror() all this for every cell I am working on.

2 calculations instead of and IF a then a division or if and just returning a 0.

At first guess I would think the time could be longer then doing things the other way, but not sure.

Anyone have any detailed knowledge of this?

 

[Aladin Akyurek]

Sunnyland,

Interesting idea... i wonder how much time and speed iserror(j13) would take. This would mean the division would have had to occur then a check for iserror() all this for every cell I am working on.

2 calculations instead of and IF a then a division or if and just returning a 0.

At first guess I would think the time could be longer then doing things the other way, but not sure.

Anyone have any detailed knowledge of this?

If you can't keep

=C4/$G4

as is, then:

=IF($G4=0,0,C4/$G4)

or

=IF($G4,0,C4/$G4)

is needed.

To improve performance, you might need to re-work other formulas in your wb, in particular the ones with volatile functions and multi-term SumProduct and CSE-formulas.

 

[ajones]

Aladin,

As always thanks for your reply.

I have reworked many of may formulas removing some volatile formulas leaving others. I pulled most of my multi-term Sumproduct and CSE (Control/ctrl/ Shift Enter - Array Formulas), etc.

The speed was not that bad until I added the if checking 6 summary worksheets. What got me was how slow things got after the checks. I know there were something like 240 checks total, but they all referenced their own sheets, No complex if statements etc. These formulas don't have to check and calculate until everything else is done.

As you did not mention my -IF($g4<>0,c4/$g4,0), does that mean it could be slower?

I liked you suggestion of =IF($G4,0,C4/$G4), I even wondered if it would be faster.


I think I read in some versions of Excel the name of the worksheet would determine if Excel calculated it first or did another one first. My guess is that won't help me but I would not be opposed to putting numbers or letters in front of worksheet tabs if needed.


thanks again for everyones thoughts.


Alan

 

[Aladin Akyurek]

Aladin,

As always thanks for your reply.

I have reworked many of may formulas removing some volatile formulas leaving others. I pulled most of my multi-term Sumproduct and CSE (Control/ctrl/ Shift Enter - Array Formulas), etc.

The speed was not that bad until I added the if checking 6 summary worksheets. What got me was how slow things got after the checks. I know there were something like 240 checks total, but they all referenced their own sheets, No complex if statements etc. These formulas don't have to check and calculate until everything else is done.

OK. I suppose you have lots of formulas which together affect the speed.

As you did not mention my -IF($g4<>0,c4/$g4,0), does that mean it could be slower?

It could be but all that shouldn't constitute a significant difference. The problem I have with <>0 is that it's true of text like "".

I liked you suggestion of =IF($G4,0,C4/$G4), I even wondered if it would be faster.

See above.

I think I read in some versions of Excel the name of the worksheet would determine if Excel calculated it first or did another one first. My guess is that won't help me but I would not be opposed to putting numbers or letters in front of worksheet tabs if needed...

Yes, the worksheet calculation sequence, if not optimal, would affect the performance. To optimize the sequence, you'll need a tool like FastExcel.

 

[ajones]

I am not to worried with something like IF($g4<>0,c4/$g4,0) that that g4 could be text like "". In my case g4 is a calculation of a calculation type thing. If it is returning text at this point something is really really wrong that should have been caught with other checks and statements.

Regarding FastExcel, I guess I need to go back and look at it closer. I thought most of what it did was analyze a workbook/sheet and make suggestions but not really redo stuff for you.

I based on how I built this workbook I know that sheets B, B, & C all feed into one larger sheet D. Nothing is actually calculated in, A, B, or C. After D is calculated (kinda the intermediary sheeet), then sheets E, F, G, H, I, & J would calculated off the calculations in D.

Is there something I can do to optimize the worksheets before jumping to FastExcel or is it more a behind the scenes thing?

I did try Ctrl+Alt+Shift+F9 to rebuild all dependency trees. Hoping that would help.

I don't object to getting FastExcel, but if I can do the work on my end first I kinda would rather start there first. I want the stuff in the sheet to be fairly portable and documented for others behind me.


thanks again for the info.

Alan