understanding formulas

Question

why do i need the -- before the Mid function in the following formula =SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)) , its a formula that sums up the digits of a number in cell A1?

Answer 1

The MID is used to parse out the characters one at a time - but returns them as string values. The second - converts them to numbers - but then they are negative, so the first - makes them positive.

This would return 0 because of the strings

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

This would return the negative of the sum of the digits

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

This will also return the correct sum of the digits

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

This will also return the sum of the digits - but apparently, multiplying is too processor intensive for some Excel fanatics' tastes. Multiplying is my preferred approach for when I need to convert booleans or strings to numbers

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

Answer 2

Have you used the correct, full array formula needed? Enter with Ctrl-Shift-Enter:

=SUM(MID(K61,LARGE(ISNUMBER(--MID(K61,ROW($1:$25),1))*ROW($1:$25),ROW($A$1:INDEX($A:$A,COUNT(--MID(K61,ROW($1:$25),1))))),1)*10^(ROW(INDEX($1:$25,COUNT(--MID(K61,ROW(INDEX($1:$25,1,1):

INDEX($1:$25,LEN(K61),1)),1)),1):INDEX($1:$25,1,1))-1))

Answer 3

Hi All

I picked this goody from Mr. Mike Girvin (you tube - Excel

Magic Trick 489: Extract Only Numbers From Text String Array Formula)

and said he got this tid-bit from Ron Coderre (MVP) by posting question on Mr Excel.

I am using MS Office 2013 (excel) and when pressing the "F9" and when the below text is highlighted

I get a message "This formula is too long. Formulas should not be longer than 8192 characters" and the data I am using is one word with a number "mon1" or "Month1"

Data Set in excel in K61:  Mon1 or month1

=ROW(INDIRECT("1:"&LEN(K61)))

Any ideas, it seems to be something with LEN Function. I am

using this for business plan and your insight/help is appreciated.

Cheers

Damon

Answer 4

Hi,

That is to convert numbers stored as text back to numbers.  You can also write the formula as follows:

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

Hope this helps.

Answer 5

many thx, david