Which is faster calculating?

[Trouttrap2]

Can anybody tell me how I can determine which formula solution is the faster calculating? There are always several ways of constructing a formula, and some are faster calculating. How do we know which is the fastest?

 

[MrKowz]

What I do is make 1000 entries of a formula, hit recalculate, and time it with a stopwatch.

However, a good way to know what formulas are faster than others is to first look at how it is evaluated. Array-evaluated formulas such as SUMPRODUCT or any Array-entered formulas are going to be inheritantly slower than any other formula.

Are there any particular formulas in question?

 

[Trouttrap2]

Thanks for idea. I am wondering about these formulas. Which would be faster:

=SUMPRODUCT(($A$2=YEAR($A$10:$A$20))*(MONTH(1&$B$2)=MONTH($A$10:$A$20)))

=SUMPRODUCT(--(TEXT(A10:A20,"yyyymmmm")=A2&B2))

These both count the number of date records with criteria of Year and Month.

 

[obiron]

Following on from MrKows,

In the Formula Evauation, anything which shows as 'Volatile' may cause you issues. Although it may not be slower in itself, it will be forced to reclaculate every time the sheet calculates which may result in unneccesary calculations. The two most common volatile functions are VLOOKUP() and INDIRECT()

Also you may be able to speed things up by using an interim step. E.g. If you have a number of INDEX() functions which all need to MATCH() the same value, it is more efficient to store the MATCH() value in the sheet and use the cell value where you are currently using MATCH(). This way the MATCH() only has to be performed once.

 

[MrKowz]

I would expect the latter of the two to calculate faster, as it is has less calculations it needs to do.

If we break them down, the first formula has to:

=SUMPRODUCT(($A$2=YEAR($A$10:$A$20))*(MONTH(1&$B$2)=MONTH($A$10:$A$20)))

1. Determine the Year of A10:A20
2. Compare the Years calculated with A2 to return an array of TRUE/FALSE
3. Calculate the Month of A10:A20
4. Calculate the Month of 1&B2
5. Compare the Months calculated with the Calculation returned by Step 4 to return an array of TRUE/FALSE
6. Multiply each entry in the array together to return a 1-dimensional array of 1/0.
7. Add each element of the array together

If we break down the second formula:

=SUMPRODUCT(--(TEXT(A10:A20,"yyyymmmm")=A2&B2))

1. Convert A10:A20 into a string of yyyymmmm
2. Concatenate A2&B2
3. Compare the strings with the concatenation, returning an array of TRUE/FALSE
4. Convert the TRUE/FALSE to an array of 1/0.
5. Add each element of the array together.

 

[Trouttrap2]

Thanks Mr. Kowz,
Breaking it down as you did shows that there are indeed more steps to calculate in one over the other. It's a shame there isn't some "timer" built in "properties" somewhere. That would be handy.