REPT()'s Order of Evaluation

[UniMord]

I often use REPT in place of an IF where I need the text value only if TRUE.
Question: Does REPT evaluate the text argument only if the number_times is TRUE, or does it evaluate the arguments in order? Why does this matter? Let's say I have a formula that looks like this:

=REPT(intense_formula_that_returns_text, intense_condition_that_returns_boolean)

If Excel evaluates number_times first, and only evaluates the text argument if TRUE, this is a sensible formula. However, if Excel evaluates from left to right, so that, even before it gets to the number_times condition, this is wasteful, and I'm better off using an IF, which will not evaluate the value_if_true, if the test failed.

I see that Evaluate Formula does evaluate the text argument first, but, is this proof positive that this is, in fact, how Excel evaluates REPT for real?

Thank you!

 

[Chihiro]

Try using Evaluate Formula tool. It should generally, honor the order of how formula is resolved.

Though I'm pretty sure that first argument within REPT must be resolved before the right argument.

 

[NARAYANK991]

Hi ,

I think that a formula is always evaluated by traversing the formula from left to right.

For example , if you have a formula such as :

=SMALL(intense_formula_which_evaluates_to_an_array , intense_formula_which_evaluates_to_a_number)

the evaluation of the formula will start by evaluating the array , before it evaluates the number.

Consequently , if a formula uses REPT to repeat text a specified number of times , the evaluation of the text to be repeated will occur first , before the evaluation of the number of times the repetition should be done.

Narayan

 

[UniMord]

In addition to Evaluate Formula resolving REPT from left to right, I see that the text argument will return an error, even if number_times = 0. For example, =REPT(A1 A1, 0) returns #NULL!.

My only doubt is, that REPT probably wasn't built to be an IF, the way we're using it, so, it may be evaluating both arguments, regardless.

 

[NARAYANK991]

In addition to Evaluate Formula resolving REPT from left to right, I see that the text argument will return an error, even if number_times = 0. For example, =REPT(A1 A1, 0) returns #NULL!.

My only doubt is, that REPT probably wasn't built to be an IF, the way we're using it, so, it may be evaluating both arguments, regardless.

Hi ,

I do not know which version of Excel you are using ; I am using Excel 2010 for Windows , and I do not get any error result from any of the following :

=REPT(A1 , 0) ----- A1 is a cell reference

=REPT(A1 A1 , 0) ----- what you have shown in your post

=REPT("A1" , 0) ----- the first parameter is a text string

Narayan

 

[UniMord]

Sorry, I meant =REPT(A1 A2, 0)
The point being, an invalid reference. I could have used 1/0, #N/A, or anything else that results in an error as well, the result is the same, the error shows up, even if number_times = 0.

 

[NARAYANK991]

Hi ,

It clearly shows that the evaluation of the first parameter takes place before the evaluation of the second parameter ; secondly , where the formula displays an error value , it is solely because of an error in the first parameter ; the second parameter being 0 does not generate an error.

I am not able to understand the context in which such differences between one function and another become significant ; is it that your workbook needs every possible bit to be optimized ?

Is it for display purposes ?

What will be the outcome if you just had your large formula , without either an IF or a REPT wrapped around it ?

Narayan

 

[UniMord]

It's not a matter of great import. I simply prefer to use REPT in place of IF when I don't want to return anything if FALSE. IF requires me to return at least an empty string (""). REPT, returns absolutely nothing if number_times = 0.

However, I'm not going to waste calculation on REPT, when IF can accomplish the same thing without needlessly evaluating the text when the condition is FALSE, so, when the output requires any significant calculation, and the overhead is of concern, I'll stick to IF.