The Trek BBS

The Trek BBS (http://www.trekbbs.com/index.php)
-   Science and Technology (http://www.trekbbs.com/forumdisplay.php?f=36)
-   -   Microsoft Excel question (http://www.trekbbs.com/showthread.php?t=211867)

Tiberius May 7 2013 01:18 AM

Microsoft Excel question
 
Okay, so I was randomly poking around on Futility Closet and I came across this:

http://www.futilitycloset.com/2006/1...deney-numbers/

Now, I want to play around with this idea and see it for myself. Rather than manually work out every single number, I'm planning on using an Excel spreadsheet.

My plan for working it out is this.

In Column A, I will have the number, starting at 1 and increasing through 2, 3 and so on.

In Column B, I will have the number cubed.

Now, in Column C, I want to take the number from Column B and add the individual digits together. So if Column B had 512, Column C would return the result of 5+1+2.

And therein lies my question. Is there a formula that can do this? So far all I can think of is to use a clunk procedure in which I divide each number by 100 and ignore the decimal, then use that number to get rid of the hundreds column and then divide by ten and ignore the decimal and so on. A rather clunky solution and I'd like to know if there's anything more streamlined.

Asbo Zaprudder May 7 2013 01:06 PM

Re: Microsoft Excel question
 
Does http://support.microsoft.com/kb/214053 work for you?

Tiberius May 7 2013 10:40 PM

Re: Microsoft Excel question
 
Unfortunately, that didn't seem to work... :( Came up with 64 giving the result of 7.

Asbo Zaprudder May 8 2013 05:03 AM

Re: Microsoft Excel question
 
Try this instead:

=SUMPRODUCT(MID(B1,ROW(OFFSET($A$1,,,LEN(B1))),1)+ 0)

assuming the cell containing the digits that you want to sum is B1.

Tiberius May 8 2013 08:42 AM

Re: Microsoft Excel question
 
That did it! Thanks!

No idea how the formula works though. Where did you find it?

Asbo Zaprudder May 8 2013 09:04 AM

Re: Microsoft Excel question
 
Quote:

Tiberius wrote: (Post 8057443)
That did it! Thanks!

No idea how the formula works though. Where did you find it?

http://chandoo.org/wp/2011/03/18/cal...s-in-a-number/

The site explains how it works.

Tiberius May 8 2013 09:58 AM

Re: Microsoft Excel question
 
Cheers for that!


All times are GMT +1. The time now is 05:21 PM.

Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
FireFox 2+ or Internet Explorer 7+ highly recommended.