0

Here is the screenshot, it's only an example since I can't post the original one:

Here is the screenshot, it's only an example since I can't post the original one

I made this worksheet to cross-check the employee present with another sheet that has information whether this employee are planned to work on such date or not.

The function I used in D1,D2,D3 columns are like this, example on D1:

=IF([@Date]=DATE(2016,7,1),VLOOKUP([@ID],(*array on another sheet*),3,false),"")  

It checks if the date is 7/1/16 then it will return the result, if not it will return blank, and this function is copied until the bottom of the table (around 250000th row) on 30 columns (from D1 to D30/D31). Is there a way to skip the function if the logical value returns false so that it will consume less memory and faster since it will have so many field that has function return blank value ?

2
  • 1
    My understanding is that Excel is optimized to evaluate only what it needs. Rather than completely evaluating every branch in the formula and then applying the logic, it works its way through the formula structure until a conclusion is reached, and doesn't bother evaluating branches it doesn't need. (Why error conditions on unused branches don't kill the formula.) For massive spreadsheets, structure your formulas so the most likely cases and least computation are disposed of first rather than in buried "ELSE" conditions. In your example, better to do: IF(@date<>date,"",vlookup).
    – fixer1234
    Jul 4 '16 at 5:02
  • Actually, it occurred to me that the order probably doesn't make a difference in this case because there is only one condition. It needs to be evaluated, and the result leads to one path or the other, so the sequence shown at the end of my other comment probably produces the same result. If you had nested IFs, you would want to use the outer one to resolve the greatest number of cases with the least amount of work, and use the same strategy for each successive layer. Use the inner-most branches for the heavy-duty computing that applies to the fewest cases.
    – fixer1234
    Jul 4 '16 at 20:01
1

No, there is no way in Excel to control how formulas are evaluated, however you have some ways to improve:

  • do you really need a separate column for each day? Instead of hard coding date in the formula (DATE (...)) you can refer to the first column ([@date])
  • you can turn off automatic calculation and calculate manually only when you need it
  • save your file as a binary workbook (.xlsb) it requires less space and has a better performance (downside is compatibility).
2
  • No, I made it such so that I can filter it easily. I have another version which I copy the function manually only on row that necessary to be calculated. So, I try to made this version so I only type one function on the top row and copy it to all row, but it was expensive. Jul 4 '16 at 4:25
  • 1
    Why this layout has any advantage during filtering?? The issue here is not the performance of Excel, but the layout of your table. You're looking for optimization at the wrong place. You should optimise your table's layout instead of the functions. Whatever you can do with your current table is also possible with an optimised one. Jul 4 '16 at 4:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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