Understanding Formula

[zaska]

Hi all,

I would request anyone to unravel the following formula as i am unable to understand it's logic.


=SUMPRODUCT(MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)+0)


Thank you

 

[SydneyGeek]

Does this come from a particular thread? It's hard to say much without the context

Denis

 

[zaska]

Hello sir,

The context is to findout the sum of all digits in a cell and i am unable to understand this formula. I don't have any clue about MID , ROW, OFFSET and SUMPRODUCT hence missing the logic behind

Thank you

 

[SydneyGeek]

Let's assume the number 1235 is in A1.

Start with the OFFSET. OFFSET($A$1,,,LEN(A1)) creates a range with the same height as the number of characters in A1.
The ROW function then returns the row values of that bogus range, starting with 1, so you get {1,2,3,4} representing the positions in the value of A1.
MID will pull each of those positions in turn so you get effectively, MID(A1,1,1);MID(A1,2,1);MID(A1,3,1);MID(A1,4,1) -- the four digits in A1, each isolated as a single digit.
SUMPRODUCT loops through, adding "1","2","3","5" together to give 11, the final answer. Note: the +0 is a way to convert text "numbers" to numbers

Denis

 

[Aladin Akyurek]

Hi all,

I would request anyone to unravel the following formula as i am unable to understand it's logic.


=SUMPRODUCT(MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)+0)


Thank you

It's identical to:

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

The ROW bit creates an integer array of 1 to the length of the value in A1...

If A1 is 6718, the length of A1 is 4. So we need an integer array of 1, 2, 3, and 4.

The OFFSET bit will produce:

{1;2;3;4}

Fed to MID, we get:

MID(A1,{1;2;3;4},1)

===>

{"6";"7";"1";"8"}

which is an array of text numbers.

If you coerce this using +0, we get:

{6;7;1;8}

Fed to SumProduct, we get: 22

 

[zaska]

Sir,

I tried using only the offset in excel sheet and it turned zero. I am still in confusion. Can you kindly Show the calculation as to what exactly the formula does.

I didn't understand where the offset formula creates a equal height.

Thank you

 

[zaska]

It's identical to:

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

The ROW bit creates an integer array of 1 to the length of the value in A1...

If A1 is 6718, the length of A1 is 4. So we need an integer array of 1, 2, 3, and 4.

The OFFSET bit will produce:

{1;2;3;4}

Fed to MID, we get:

MID(A1,{1;2;3;4},1)

===>

{"6";"7";"1";"8"}

which is an array of text numbers.

If you coerce this using +0, we get:

{6;7;1;8}

Fed to SumProduct, we get: 22

Thank you sir. I understood the formula clearly.