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".
CodePudding user response：
#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 |
CodePudding user response：
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