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

#### ajones

##### Board Regular
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.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.

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?

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.

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

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.

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

Replies
0
Views
317
Replies
3
Views
6K
Replies
17
Views
1K
Replies
1
Views
365
Replies
17
Views
929

1,203,064
Messages
6,053,318
Members
444,653
Latest member
Curdood

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back