Hi all

I can't explain myself why this calculation gives strange results if the field "range" do not start from A0.

That field contains something like A0-A7 and I want the result to be:

A0

A1

A2

A3

A4

A5

A6

A7

There can be many calculations, but I writed this one:

Let([

start = MiddleWords ( range ; 1 ; 1 ) ;

end = MiddleWords ( range ; 2 ; 1 ) ;

string = Left ( 123456789 ; Length ( 10^( end - start ) ) - 1 )

];

start & Substitute ( string

; [ 1 ; ¶ & SerialIncrement ( start ; 1 ) ]

; [ 2 ; ¶ & SerialIncrement ( start ; 2 ) ]

; [ 3 ; ¶ & SerialIncrement ( start ; 3 ) ]

; [ 4 ; ¶ & SerialIncrement ( start ; 4 ) ]

; [ 5 ; ¶ & SerialIncrement ( start ; 5 ) ]

; [ 6 ; ¶ & SerialIncrement ( start ; 6 ) ]

; [ 7 ; ¶ & SerialIncrement ( start ; 7 ) ]

; [ 8 ; ¶ & SerialIncrement ( start ; 8 ) ]

; [ 9 ; ¶ & SerialIncrement ( start ; 9 ) ]

)

)

This calculation works but ONLY if the first word contains a 0. A range A1-A2 gives a strange result.

Now I do not want a ( different ) working calculation, I only wish to know where is the error or if SerialIncrement ( ) can give this strange result :)

Daniele

It's not the SerialIncrement that's the problem, it's the Substitute

Try reversing the order of the substitutions:

; [ 9 ; ¶ & SerialIncrement ( start ; 9 ) ]

; [ 8 ; ¶ & SerialIncrement ( start ; 8 ) ]

; [ 7 ; ¶ & SerialIncrement ( start ; 7 ) ]

; [ 6 ; ¶ & SerialIncrement ( start ; 6 ) ]

; [ 5 ; ¶ & SerialIncrement ( start ; 5 ) ]

; [ 4 ; ¶ & SerialIncrement ( start ; 4 ) ]

; [ 3 ; ¶ & SerialIncrement ( start ; 3 ) ]

; [ 2 ; ¶ & SerialIncrement ( start ; 2 ) ]

; [ 1 ; ¶ & SerialIncrement ( start ; 1 ) ]

Hi Tom

thank you. Although the reverse works, I do not understand why!

In the case of the range A1-A2, "string" contains 1, so I would expect a fair result in every case.

Why not? Did you know ?

Edit:

I finally understood where was the problem...

When "string" contains 1, the Substitute( ) returns a new "string" that contains: A2.

So now it go on to substitute the 2... and so on till 9.

Hi Daniele

The way you had it, the first substitution looks for "1" in your string and if it finds it replaces it with some number which depends on start, but has got to be at least 1, let's suppose it is in fact 2; the the next substitution looks for "2"in your string, but now there may be two 2's in the string - the original one and the one that the first substitution put there, so they both get replaced by 3, and so it goes on for all the following substitutions. This is not what you want.

By reversing the order of substitutions, you don't have this problem because each replacement value is at least as great as the number it is replacing and, therefore, greater than the search value for each of the following substitutions

cheers

Tom

Again thank you.

I will try to exploit in some way the inner recursivity of that function ( In fact that was my initial intention )