John Walkenbachs array formula

[Jon von der Heyden]

Hi,

A friend and I are stumped in trying to work out how this calculates the sum of the each integer in a number. Is anybody able to explain to me how this works? I'm confused around the ROW(A1:OFFSET()) segment in particular.

Many thanks,
Jon

Book6
	A	B	C	D
1	7845
2	24
Sheet1

 

[Andrew Poulsom]

This:

ROW($A$1:OFFSET($A$1,LEN(ABS(A1))-1,0)

returns an array of numbers from 1 to the length of the absolute number in A1, in this case {1,2,3,4}. The MID function returns the number at that position in the absolute value of A1, in this case {7,8,4,5}. The SUM function sums that array to retyrn 24.

 

[cmart02]

The key is on the MID function:

MID(ABS(A1),ROW($A$1:OFFSET($A$1,LEN(ABS(A1))-1,0)),1)

1) The first argument ensures that value on A1 is the absolute value
2) The second argument is where the number should be extracted from. He uses the function ROW to return an array that starts at 1 and ends at the length of the absolute value of the number on A1.
3) The last argument extracts only only number at a time

At this points, he uses the function VALUE to convert the strings of numbers returned by the MID function and then sums the array.

My own version is:

=SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

This will only sum positive values. If your number were -7845, it'd return an error. I'll leave you to get it sorted If you can't solve, let me know and I'll give the answer.

 

[Jon von der Heyden]

Wow, that's clever! Thanks for the explanations, we understand it now.

 

[barry houdini]

You could avoid CTRL+SHIFT+ENTER by using

=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

 

[cmart02]

Barry,

I like the one you've just posted!