My lottery spreadsheet is getting slower and slower to recalculate every time I update it.
For a while, I could get it to speed up by removing all the duplicated conditional formats, but now even that isn't working. So, when I saw the following article I pounced on it"
http://chandoo.org/wp/2014/01/17/big-trouble-in-little-spreadsheet/
The article identifies two problems:
. range issues
. extensive use of SUMIF, COUNTIF, SUMPRODUCT, especially if pointed at whole ranges
The second one is my problem. For example, 5,438 cells in the worksheet contain COUNTIF formulas. Sigh. I guess I am going to have to learn how to use pivot tables.
For a while, I could get it to speed up by removing all the duplicated conditional formats, but now even that isn't working. So, when I saw the following article I pounced on it"
http://chandoo.org/wp/2014/01/17/big-trouble-in-little-spreadsheet/
The article identifies two problems:
. range issues
. extensive use of SUMIF, COUNTIF, SUMPRODUCT, especially if pointed at whole ranges
The second one is my problem. For example, 5,438 cells in the worksheet contain COUNTIF formulas. Sigh. I guess I am going to have to learn how to use pivot tables.