1

Here is an example given by google of a Named function using recursion

=IF(ISERROR(FIND(" ", str)), str, REVERSE_WORDS(RIGHT(str, LEN(str)-FIND(" ", str)))&" "&LEFT(str, FIND(" ",str)-1))

This function will take "1 2 3 4" and the final output will be "4 3 2 1" but I am trying to understand exactly why this is occurring specifically by taking it step by step and this is what I have so far:

Imaginary Debugging  
Step 1
iserror false
Step 2
REVERSE_WORDS( "2 3 4" )
Step 3
iserror false
Step 4
REVERSE_WORDS( "3 4" )
Step 5
iserror false
Step 6
REVERSE_WORDS( "4" )
Step 7?
iserror true so return 4?
Step 8?
???

I don't understand exactly how the final output becomes "4 3 2 1" can someone please write out the remaining steps or correct them for me to visualize since I can't debug/evaluate functions in google sheets. I understand &" "&LEFT(str, FIND(" ",str)-1)) will output 1 then 2 then 3 and then not trigger the forth time because of the iserror but it seems like the output should be "4 1 2 3" or just "4 1" or "4 3".

TheMaster
  • 45,448
  • 6
  • 62
  • 85
CodeCamper
  • 6,609
  • 6
  • 44
  • 94

2 Answers2

3

Recursion is a programming strategy, where a function calls itself. If a function is made to call itself, it can call itself indefinitely. For recursion to return a valid value, the exit strategy should be inside the function itself.

IF(ISERROR(FIND(" ", str)), str, REVERSE_WORDS(RIGHT(str, LEN(str)-FIND(" ", str)))&" "&LEFT(str, FIND(" ",str)-1))

Here, the exit strategy is provided ISERROR. During each recursion, the function checks, if FIND() throws a error, if it throws a error, the function returns the string, else it keeps calling itself. Whenever it calls itself, it leaves a value.

REVERSE_WORDS()&" "&LEFT(str, FIND(" ",str)-1))

Here, it leaves LEFT(str, number of characters) on each call. The number of characters is determined by finding the first space in the string. In the case of

1 2 3 4

Imaginary Debugging  
Step 1
iserror false
Return REVERSE_WORDS( "2 3 4" )& " 1"
Step 2
REVERSE_WORDS( "2 3 4" )
iserror false
Return REVERSE_WORDS( "3 4" )& " 2"
Step 3
REVERSE_WORDS( "3 4")
iserror false
Return REVERSE_WORDS( "4" )& " 3"
Step 4
REVERSE_WORDS( "4" )
iserror true so 
Return "4"

Note that the first return is the real return or the return we receive from calling REVERSE_WORDS( "1 2 3 4" ). That return is

REVERSE_WORDS( "2 3 4" )& " 1"

The REVERSE_WORDS( "2 3 4" ) call in that first return returns:

REVERSE_WORDS( "3 4" )& " 2"

Combined, the first return can be written as,

REVERSE_WORDS( "3 4" )& " 2"& " 1"

If we keep substituting(recursing), we get,

"4"&" 3"&" 2"&" 1"

which is

4 3 2 1
  • Why isn't it 1 2 3 4?

Because of the order of operation. If we change

REVERSE_WORDS()&" "&LEFT()

to

LEFT()&" "&REVERSE_WORDS()

We'll get 1 2 3 4

TheMaster
  • 45,448
  • 6
  • 62
  • 85
1
#1 =IF(ISERROR(FIND(" ", str)), str, 
#2 REVERSE_WORDS(RIGHT(str, LEN(str)-FIND(" ", str)))
#3 &" "&
#4 LEFT(str, FIND(" ",str)-1))

#1 If there is no space in the input, return the input.

#4 Extract the first element from the input and place it at the end of the output

#2 Extract the string without the first element from the input and recur

#3 Concat the return value of recursion (#2) and the first element (#4) with a space

1st level:

Step Result
Input 1 2 3 4
#1 False
#4 1
#2 REVERSE_WORDS("2 3 4")
#3 Return of 2nd level 1

2nd level:

Step Result
Input 2 3 4
#1 False
#4 2
#2 REVERSE_WORDS("3 4")
#3 Return of 3rd level 2

3rd level:

Step Result
Input 3 4
#1 False
#4 3
#2 REVERSE_WORDS("4")
#3 Return of 4th level 3

4th level:

Step Result
Input 4
#1 TRUE
#4 4
#2
#3 4

Finally, the return values are concatted as follows:

Level Return
4th 4
3rd 4 3
2nd 4 3 2
1st 4 3 2 1
idfurw
  • 5,727
  • 2
  • 5
  • 18