Excel Challenge - Adding the digits of a number

amar.cbs
Posts: 3
Joined: Wed Jul 04, 2012 6:07 pm

Excel Challenge - Adding the digits of a number

Postby amar.cbs » Wed Jul 04, 2012 6:26 pm

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

Tags:

admin
Site Admin
Posts: 12
Joined: Sat Feb 11, 2012 7:35 pm

Re: Excel Challenge - Adding the digits of a number

Postby admin » Thu Jul 12, 2012 10:55 am

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!

amar.cbs
Posts: 3
Joined: Wed Jul 04, 2012 6:07 pm

Re: Excel Challenge - Adding the digits of a number

Postby amar.cbs » Fri Jul 13, 2012 11:12 am

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


Return to “Excel Tips”



cron

Disclaimer

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.