Thread: Breaking up a number

Welcome to the forum.

Please take a few minutes to read the forum rules, and then start a new thread in one of the question forums -- this isn't one.

Thanks.

I can't move the thread (not a Mod), so can you repost in the Worksheet Functions forum?

thread moved.

It's not clear where the values are etc but if given use of XL2007 if you have say 1,2,4,8 listed in B1:E1 and the value of interest (eg 15 or 9 etc...) in A2 then

EDIT: revised

Code:

B2: =B$1*LEFT(RIGHT(TEXT(DEC2BIN($A2),"0000"),COLUMNS($B2:B2)),1)
copied to D2

is that what you want ?

It does, and DO's suggestion does that:

Code:

       A- B C D E
   1      1 2 4 8
   2    0 0 0 0 0
   3    1 1 0 0 0
   4    2 0 2 0 0
   5    3 1 2 0 0
   6    4 0 0 4 0
   7    5 1 0 4 0
   8    6 0 2 4 0
   9    7 1 2 4 0
  10    8 0 0 0 8
  11    9 1 0 0 8
  12   10 0 2 0 8
  13   11 1 2 0 8
  14   12 0 0 4 8
  15   13 1 0 4 8
  16   14 0 2 4 8
  17   15 1 2 4 8

And alternative, in B2 and copied across and down:

=B$1 * MID(DEC2BIN($A2, 4), 5 - COLUMNS($B2:B2), 1)

If you need to generate the values without headers then if A1 holds 9

Code:

B1: =2^(COLUMNS($B1:B1)-1)*LEFT(RIGHT(TEXT(DEC2BIN($A1),"0000"),COLUMNS($B1:B1)))
copied across to E1 (B1:E1 copied down as nec.)

edit:

or to use shg's more succinct version simply modify along same lines as above re: use of 2^ multiplier, ie:

Code:

B1: =2^(COLUMNS($B1:B1)-1)*MID(DEC2BIN($A1,4),5-COLUMNS($B1:B1),1)
copied across to E1 (B1:E1 copied down as nec.)