Speed difference in formula calculation if I use a static "indicator" column for my IF statement criteria (see pic)?

[d0rian]

I created a super-simplified pic of my Q below, but TLDR: I often want dozens of formula columns to calculate ONLY if a certain (sometimes-complex) criteria is met, so I use a standard =IF(criteria , if_true , if_false) syntax.

Now because that criteria can often be complex, in order to keep my formulas neater, I'll often create an "indicator" column that returns "x" if the criteria is met, and then my dozens of formula columns will simply all begin with "=IF($B1="x","

This saves the trouble of having to include the much more complex criteria formula in the IF statement for all of my main formula cells.

What I want to know, however, is if I'm sacrificing SPEED when I do this -- i.e. I'm adding in an extra formula in an indicator column so that I can keep all my other formulas neater...but is this causing any unnecessary bloat?

*So re: illustrative image below, in a nutshell what I want to know is whether there's any speed difference in structuring my formulas as you see them in E:G vs what you see in I:K.

• In I:K, the IF statement checks the same "source cell" ($A2) each time
• In E:G, the IF statement just checks the indicator column ($B2)

Hopefully goes without saying, but this is an insanely simplified version of the formulas I'm actually using so of course there'd be no speed difference in the image below. But we're talking about thousands of rows of formulas, and the "criteria" formula that in this image is just a simple ISNUMBER() check is often a rather complex multi-variable beast.

 

[offthelip]

I don't know the answer to your question, but I would expect a helper column to faster because there would be fewer if statements overall. However what I think will make more difference is to make use of the boolean type which take less processing than the variant type you are using for your column. i.e use the two values of TRUE and FALSE for your helper column.
In this case you can get rid of the if statement in the helper column because your condition is so simple ie:
In column B all you need is :

ISNUMBER($A2)

What ever your condition in column B if YOU make it a TRUE/FALSE condition you can then simplify the equations in E,F G etc to:

IF($B2,len($A2),"")

I would expect that to be a bit faster.

 

[d0rian]

I don't know the answer to your question, but I would expect a helper column to faster because there would be fewer if statements overall. However what I think will make more difference is to make use of the boolean type which take less processing than the variant type you are using for your column. i.e use the two values of TRUE and FALSE for your helper column.
In this case you can get rid of the if statement in the helper column because your condition is so simple ie:
In column B all you need is :

ISNUMBER($A2)

What ever your condition in column B if YOU make it a TRUE/FALSE condition you can then simplify the equations in E,F G etc to:

IF($B2,len($A2),"")

I would expect that to be a bit faster.

Thanks for this information; I rarely (almost never) use Boolean logic/formulas (almoast always just the traditional IF formula syntax.) I did a quick Google search about Boolean formulas (vs 'variant' or the traditional IF syntax), but didn't find much relating to their being largely considered better/faster. (Only this page, which kinda hinted at it, but wasn't conclusive about it.) Is that widely considered to be the case?

 

[offthelip]

I was interested in this question so I have done some tests using the microtimer as documented in this link:

https://docs.microsoft.com/en-us/of...rmance/excel-improving-calcuation-performance

I set up sheet1 with this code in B2 which I copied down to B1000

=IF(ISNUMBER(A2),"","x")

Column A had alternating "1" and "t" all the way down

in a sheet2 I had this code in

=ISNUMBER(A2)

in B2 to B1000 with the same data in column A.
I then ran the sheettimer routine 100 times on each sheet and took the average.
the results are:
Sheet1 average (IF) =0.025386 seconds
sheet 2 average (boolean) =0.0251883 seconds
i.e about 0.2 milliseconds faster for 100 calculations
so not a lot of difference, unless you are doing millions of these

<tbody>
</tbody>

 

[d0rian]

Thanks for putting that time in (and I'll follow-up with some testing of my own on this too). Two thoughts:

1) While your testing suggested a negligible difference unless the # of calculations was much higher, it could also be material if the IF statement being evaluated was something more complex than a simple ISNUMBER, right? For instance, the criteria I'm using in my actual sheet (not the simplified version I created for this post) looks something like this:

- Col A: a bunch of values
- Col B: a bunch of values
- Col C: =IF(AND(ISNUMBER(A1),A1>0,ISNUMBER(B1),B1>0,OR(AVERAGE(A1,B1)>100,AVERAGE(A1,B1)<25)),"x","")

In plain English >> the values in Col-A and Col-B must be (i) real numbers greater than 0, AND (ii) whose average is EITHER greater than 100 or less than 25.)

And then in Cols D:H I have my "main" formulas (which I only want to run if the condition is met). So the question from my original post boils down to whether those formulas in D:H will run faster:
a) if I use the Col-C Helper column, in which case each of the D:H formulas will look like: =IF($C1="x",formula_if_true,""), OR
b) if I DON'T use the Col-C helper, in which case each of the D:H formulas will look like: =IF(AND(ISNUMBER($A1),$A1>0,ISNUMBER($B1),$B1>0,OR(AVERAGE($A1,$B1)>100,AVERAGE($A1,$B1)<25)),formula_if_true,"")

If I understand what you're suggesting in your first reply in this thread, it's that you think I should keep the Helper column C, but in Boolean form it would ditch the IF function and look like:

=AND(ISNUMBER(A1),A1>0,ISNUMBER(B1),B1>0,OR(AVERAGE(A1,B1)>100,AVERAGE(A1,B1)<25))

And then all of my D:H formulas would look like:

=IF($C1,formula_if_true,"")

Do I have that right?​

2) Secondly, now that I've spelled out in more explicit detail the issue I'm working on, do you still think using the helper column should be faster? What I don't know is how Excel stores the results of formulas for use in other dependent formulas. And what prompted me to initially ask this Q was that I didn't know whether by creating a helper column in C I was essentially doubling (or more) the number of formulas/arguments I was asking Excel to evaluate in my D:H columns. Like if I went the no-helper-column route (what you see in 1(b) above) -- and literally typed in the IF criteria of IF(AND(ISNUMBER($A1),$A1>0,ISNUMBER($B1),$B1>0,OR(AVERAGE($A1,$B1)>100,AVERAGE($A1,$B1)<25)) in each of D1, E1, F1, G1, and H1, does Excel recognize that the criteria is the same and therefore simply evaluate it ONCE, using the result in each of D1 thru H1? Vs. if I go the helper-column route (1a above, or your suggested Boolean version), I worried that Excel might then have to separately evaluate the helper column for EACH of D1, E1, F1, G1, and H1. Or to put it another way, I guess it boils down to whether it's more processor-intensive to evaluate a FORMULA rather than static cells. Whew.

 

[offthelip]

In answer to 1) Yes

In answer to 2) I think the only way to really find out the answer is to use the microtimer in the link I gave you to test out the two alternatives. I would guess that Excel isn't clever enough to work out that it has already calculated the value it needs (but in another cell), but i would be really interested to know .
If you do the investigation do please post bask here with the results.
I use helper columns a lot because I find it makes the worksheet easier to follow ( because the equations are shorter), I would like to know if I am paying a penalty by using them!!