Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

My scenario is that I have a "Dashboard" style sheet that looks up data from a large table based on the cell in column B which is always to its left. The cell in column B contains an INDEX, MATCH, IF, array formula to pull unique names from the table where the "Group" is selected. This list is populated down and is populated based on a drop down selection of the different "Group" choices. It can return up to 20 names based on the "Group".

I have 17 cells with formulas to the right which all could return an error if the data is not there. So I have preceded them with an IFERROR() statement. My question is if I precede them with an IF() statement would that speed up the calculations because the IF statement would read if True then give blank, if false then calculate the formula.

Logically it would seem so to me, but I wanted to know if any of you feel the same way. Thanks. Perhaps this is more for a meta discussion.

p.s. Here is one of 9 of the most complex formulas I am using.

=IFERROR((SUMIFS(INDIRECT("tblData["&N$7&" Top Box Num]"),INDIRECT("tblData["&$B$7&"]"),$B$16,INDIRECT("tblData[Date]"),">="&$C$5,INDIRECT("tblData[Date]"),"<="&$D$5))/(SUMIFS(INDIRECT("tblData["&N$7&" Den]"),INDIRECT("tblData["&$B$7&"]"),$B$16,INDIRECT("tblData[Date]"),">="&$C$5,INDIRECT("tblData[Date]"),"<="&$D$5)),"")

Is the above formula slower than the formula below when True?

=IF(OR($C$16=0,C16=""),"",(SUMIFS(INDIRECT("tblData["&N$7&" Top Box Num]"),INDIRECT("tblData["&$B$7&"]"),$B$16,INDIRECT("tblData[Date]"),">="&$C$5,INDIRECT("tblData[Date]"),"<="&$D$5))/(SUMIFS(INDIRECT("tblData["&N$7&" Den]"),INDIRECT("tblData["&$B$7&"]"),$B$16,INDIRECT("tblData[Date]"),">="&$C$5,INDIRECT("tblData[Date]"),"<="&$D$5)))

The formulas essentially add up the numerator values and divide that by the denominator values where the conditions are true from a 20,000 row table and return a percent score.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

=IF does 'short circuit' but for for speeding up performance I would suggest http://msdn.microsoft.com/en-us/library/office/aa730921%28v=office.12%29.aspx. Includes a macro for timing. =INDIRECT would appear to be worth attention.

share|improve this answer
1  
Thanks pnuts, that MICROTIME macro really helped. Turns out I had two array formulas that took 1.5 seconds to run. I figured out how to make them work without being an array and they now take 0.5 seconds. Cut my total calc time down from 2 seconds to 0.5 seconds. Thanks! –  Scheballs Jun 11 '13 at 19:29
    
@Scheballs TQ. Sorry it is not a more 'definitive' answer but as you can see from the article, there are simply too many variables to take any two versions of one formula and say with confidence which will be quicker - some indication of 'probably' is the best possible without specific tests. –  pnuts Jun 11 '13 at 19:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.