Excel Formula Challenge

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Excel Formula Challenge

Post by Rudi »

This is a challenge, not an Excel Question...

Challenge 1:
Let's say cell A1 contains a numeric string. The challenge is to give me a non-array formula to sum the square of the digits.
For example if A1 = 378906, then answer would be: 9+49+64+81+0+36 = 239

Challenge 2:
Now, the challenge is for an alphanumeric string.
This time, there is no restriction that you need to give only non-array formula only.

Let's say cell A1 contains an alphanumeric string. The challenge is to give me a formula to sum the square of the digits.
For example if A1 = a3c78q90A6, then answer would be: 9+49+64+81+0+36 = 239
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
Leif
Administrator
Posts: 7193
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: Excel Formula Challenge

Post by Leif »

Challenge 2 is easy:
Spoiler
The answer is the same as the answer to Challenge 1 :grin:
Leif

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel Formula Challenge

Post by HansV »

Challenge 1:
Spoiler
=MOD(A1,10)^2+MOD(INT(A1/10),10)^2+MOD(INT(A1/100),10)^2+MOD(INT(A1/1000),10)^2+MOD(INT(A1/10000),10)^2+MOD(INT(A1/100000),10)^2
The formula can be extended for longer numbers.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Excel Formula Challenge

Post by Rudi »

Leif wrote:Challenge 2 is easy:
Spoiler
The answer is the same as the answer to Challenge 1 :grin:
:thumbup: :bash:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel Formula Challenge

Post by HansV »

Challenge 2: as an array formula, confirmed with Ctrl+Shift+Enter:
Spoiler
=SUM(IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)^2,0))
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Excel Formula Challenge

Post by Rudi »

Hans....this is your bread and butter.
You make it look so easy!
:chocciebar: :wine: and other accolades.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.