none
If Formula v. Boolean Multiplication RRS feed

  • Question

  • I have a spreadsheet with about 100,000 cells containing an if(cond,calc,0) formula and am wondering how that differs from cond*calc. With respect to performance, would cond*calc be slower because Excel evaluates both cond and calc no matter what? (Whereas, with if(cond,calc,0), Excel doesn't need to evaluate calc whenever cond is false...)
    Thursday, August 7, 2014 4:36 PM

Answers

  • I performed a test with all 1,048,576 rows filled with a formula. Recalculating the IF formula was about 10% faster than recalculating the multiplication formula.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by KCDW Thursday, August 7, 2014 6:08 PM
    • Marked as answer by monsieur t Friday, August 8, 2014 5:18 PM
    Thursday, August 7, 2014 4:46 PM

All replies

  • I performed a test with all 1,048,576 rows filled with a formula. Recalculating the IF formula was about 10% faster than recalculating the multiplication formula.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by KCDW Thursday, August 7, 2014 6:08 PM
    • Marked as answer by monsieur t Friday, August 8, 2014 5:18 PM
    Thursday, August 7, 2014 4:46 PM
  • Thanks, Hans. I didn't see a huge difference myself. I'll go with multiplication, simply because it looks more streamlined.
    Thursday, August 7, 2014 5:41 PM