How to work SUMIF function backwards to take decimal number to show active bit in status register

[TS_711]

Using Excel to analyze IEEE 488.2 communication status output report to determine active bits for machine operation. Want to use Excel to break down the large status decimal number to and highlight in sheet. Currently doing this manually is time consuming but works. Think Excel can do this but having a time trying to think up the formula to make it function correctly.

Project details -
16 bit register which has a decimal from 1 to 32,768. The status output of the machine gives 8,622. Manually working from bit 16 (32,768) to 1 (1) can determine that bits 14, 9, 8, 6, 4, 3, 2 are active by using the SUMIF function in Excel. =SUMIFS(E4:E35,C4:C35,"X")

What function would be used to be able to enter the status decimal and have excel subtract the closest number and indicate it is active, then subtract next closest number, etc. In this example the manual operation would be 8622 - 8192 (bit 14) = 430 - 256 (bit 9) = 174 - 128 (bit 8) = 46 - 32 (bit 6) = 14 - 8 (bit 4) = 6 - 4 (Bit 3) = 2 - 2 (Bit 2) = 0.

This might be better in a VBA code but I'm not sure how to get Excel to examine the closest number to the number being subtracted from.

Thank you for your time and any given assistance.

TS_711

 

[KRice]

A formula idea, depending on whether you want the binary representation in one cell or spilled down a column...and whether some post-processing is needed to deliver x's and blanks:

MrExcel_20240415.xlsx
	B	C	D	E	F	G	H
3	Status Decimal	Active	Bit			Active	Active
4	8622		1	1	0010000110101110	0
5			2	2		1	x
6			3	4		1	x
7			4	8		1	x
8			5	16		0
9			6	32		1	x
10			7	64		0
11			8	128		1	x
12			9	256		1	x
13			10	512		0
14			11	1024		0
15			12	2048		0
16			13	4096		0
17			14	8192		1	x
18			15	16384		0
19			16	32768		0
Sheet3

Cell Formulas
Range	Formula
D4:D19	D4	=SEQUENCE(16)
E4:E19	E4	=2^(D4#-1)
F4	F4	=DEC2BIN(INT(MOD(B4,256^2)/256),8)&DEC2BIN(MOD(B4,256),8)
G4:G19	G4	=MID(DEC2BIN(INT(MOD(B4,256^2)/256),8)&DEC2BIN(MOD(B4,256),8),SEQUENCE(16,,16,-1),1)
H4:H19	H4	=LET(b,MID(DEC2BIN(INT(MOD(B4,256^2)/256),8)&DEC2BIN(MOD(B4,256),8),SEQUENCE(16,,16,-1),1),IF(b="1","x",""))
Dynamic array formulas.