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
Excel Formula Challenge
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Excel Formula Challenge
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 7193
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Excel Formula Challenge
Challenge 2 is easy:
Spoiler
The answer is the same as the answer to Challenge 1
Leif
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Formula Challenge
Challenge 1:
The formula can be extended for longer numbers.
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
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Excel Formula Challenge
Leif wrote:Challenge 2 is easy:Spoiler
The answer is the same as the answer to Challenge 1
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Formula Challenge
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
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Excel Formula Challenge
Hans....this is your bread and butter.
You make it look so easy!
and other accolades.
You make it look so easy!
and other accolades.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.