Hello everybody,

I have an excel challenge. An excel formula is required to add the digits of a number.

For example,

a) If the number is 231, then answer should be 2+3+1 = 6

b) If the number is 45, then answer should be 4+5 = 9

c) Similarly if the number is 5731, then answer should be 5+7+3+1 = 16

The formula should be dynamic to fit into any of the above cases.

Give it a try - we tend to learn while doing funny things.

Kind regards,

Amardeep

I have an excel challenge. An excel formula is required to add the digits of a number.

For example,

a) If the number is 231, then answer should be 2+3+1 = 6

b) If the number is 45, then answer should be 4+5 = 9

c) Similarly if the number is 5731, then answer should be 5+7+3+1 = 16

The formula should be dynamic to fit into any of the above cases.

Give it a try - we tend to learn while doing funny things.

Kind regards,

Amardeep

Amardeep,

I posted this question to some of our Mumbai Financial Modelling faculty, a thread of replies ensued in my inbox!

Here are some of those replies -

Vikram Pandya writes -

These can be done in many ways, one of them is given below

Function SumDigits(MyNum)

For i = 1 To Len(MyNum)

MySum = MySum + CInt(Mid(MyNum, i, 1))

Next

SumDigits = MySum

End Function

Note that the above function is without any validation rule. i.e. On String or Decimal Values you will get #Value! error

Anil Bains writes -

SUMPRODUCT(MID($C$3,ROW(OFFSET($C$1,,,LEN(C3))),1)*1)

What about you folks? There are multiple ways to do this!

I posted this question to some of our Mumbai Financial Modelling faculty, a thread of replies ensued in my inbox!

Here are some of those replies -

Vikram Pandya writes -

These can be done in many ways, one of them is given below

Function SumDigits(MyNum)

For i = 1 To Len(MyNum)

MySum = MySum + CInt(Mid(MyNum, i, 1))

Next

SumDigits = MySum

End Function

Note that the above function is without any validation rule. i.e. On String or Decimal Values you will get #Value! error

Anil Bains writes -

SUMPRODUCT(MID($C$3,ROW(OFFSET($C$1,,,LEN(C3))),1)*1)

What about you folks? There are multiple ways to do this!

Thanks Vikram and Anil. These are quite useful.

Here is my solution. Let's say cell A1 contains the number, then the formula would be:

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

The above formula works only when the number is positive and without decimal places.

Here is an alternate comprehensive formula which takes care of even negative numbers and decimal values:

=SUM(IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1,0))

Kind regards,

Amardeep

Here is my solution. Let's say cell A1 contains the number, then the formula would be:

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

The above formula works only when the number is positive and without decimal places.

Here is an alternate comprehensive formula which takes care of even negative numbers and decimal values:

=SUM(IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1,0))

Kind regards,

Amardeep

Global Association of Risk Professionals, Inc. (GARP^{®}) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM^{®} related information, nor does it endorse any pass rates claimed by the provider. Further, GARP^{®} is not responsible for any fees or costs paid by the user to EduPristine nor is GARP^{®} responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM^{®}, GARP^{®} and Global Association of Risk Professionals^{®}, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA^{®}, Claritas^{®} and Chartered Financial Analyst^{®} are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to abuse@edupristine.com and we will rectify it.

2015 © Edupristine. ALL Rights Reserved.