Hexidecimal lookup

[Stuart Peters]

I know there is a way to do this I just can't figure it out.
Below is the lookup table.
I would like to enter a Hexidecimal number and have the program return all
the Fault descriptions that are applicable.
For instance If I enter the Hex value '2C'
I should get the following list:
General Fault Indicator
Temperature Indicator
Power Supply Indicator

Note: Fault value in column A in Hex
Fault value in lookup does not need to be Hex, but user input is always hex

Fault Value Description
1 Mute Indicator
2 Frequency Unlock
4 Power Supply Indicator
8 Temperature Indicator
10 VSWR Fault Indicator
20 General Fault Indicator
40 Spare
80 Spare
100 Frequency Unlock with PS OK
200 10 MHz reference Fault
400 Temperature foldback at 85 degrees
800 Temperature Shutdown at 90 degrees
1000 VSWR foldback at 1.5
2000 VSWR shutdown at 2.5
4000 Spare
8000 Spare
10000 Bandpass Filter Fault
20000 Low RF Output Fault
40000 Final PA current 1 Fault
80000 Final PA current 2 Fault

Thanks,

Stuart

 

[David Biddulph]

I know there is a way to do this I just can't figure it out.
Below is the lookup table.
I would like to enter a Hexidecimal number and have the program return all
the Fault descriptions that are applicable.
For instance If I enter the Hex value '2C'
I should get the following list:
General Fault Indicator
Temperature Indicator
Power Supply Indicator

Note: Fault value in column A in Hex
Fault value in lookup does not need to be Hex, but user input is always
hex

Fault Value Description
1 Mute Indicator
2 Frequency Unlock
4 Power Supply Indicator
8 Temperature Indicator
10 VSWR Fault Indicator
20 General Fault Indicator
40 Spare
80 Spare
100 Frequency Unlock with PS OK
200 10 MHz reference Fault
400 Temperature foldback at 85 degrees
800 Temperature Shutdown at 90 degrees
1000 VSWR foldback at 1.5
2000 VSWR shutdown at 2.5
4000 Spare
8000 Spare
10000 Bandpass Filter Fault
20000 Low RF Output Fault
40000 Final PA current 1 Fault
80000 Final PA current 2 Fault

It may be easiest to use the HEX2BIN function, and then split the bits in
the binary, perhaps with MID().

 

[Bearacade]

Ok, I looked at the problem a bit and notice you have to do a few
things,

First seperating out the digits. you can do that with LEN, MID, and
HEX2DEC

Then you have to lookup the value, but must bare in mind that certain
value return more than 1 results...

I enclosed a copy of what I think you need, you can take a look at it
and modify as needed.


+-------------------------------------------------------------------+
|Filename: HexLookup.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5071 |
+-------------------------------------------------------------------+

 

[Harlan Grove]

Stuart Peters wrote...

I know there is a way to do this I just can't figure it out.

Why? Spreadsheets aren't meant to work with hexadecimal numerals.
Trying to make them do so is a LOT of work.

Below is the lookup table.
I would like to enter a Hexidecimal number and have the program return all
the Fault descriptions that are applicable.
For instance If I enter the Hex value '2C'
I should get the following list:
General Fault Indicator
Temperature Indicator
Power Supply Indicator

Note: Fault value in column A in Hex

....

Hexadecimal 'numbers' are always text, so they need to have leading
zeros in order for, say, "9" as "00009" to be less than "10000".

Assuming your table with column headings is in A1:B21, with fault code
entered in cell G3 and first fault description in cell H3, *and* with
the Analysis ToolPak loaded so that you can use HEX2DEC and DEC2HEX,
try these formulas.

H3:
=LOOKUP(RIGHT("00000"&G3,5),RIGHT("00000"&$A$2:$A$21,5),$B$2:$B$21)

H4:
=IF(IF(H3<>"",MOD(HEX2DEC($G$3),HEX2DEC(INDEX($A$2:$A$21,MATCH(H3,
$B$2:$B$21,0))))),LOOKUP(DEC2HEX(MOD(HEX2DEC($G$3),
HEX2DEC(INDEX($A$2:$A$21,MATCH(H3,$B$2:$B$21,0)))),5),
RIGHT("00000"&$A$2:$A$21,5),$B$2:$B$21),"")

Fill H4 down into H5:H22 to cover fault code entry of FFFFF.

 

[Dana DeLouis]

Hi. Given "2C", It looks like you are trying to identify the 3rd, 4th, and
6th item in your description list.

=HEX2BIN("2C",8)
00101100

Here's just another idea...
Give the range name "n" the following:
=HEX2DEC("2c")
44

I would use a custom function, something like the following.
Those values that return 1 are associated with what you are looking for.

Function BitAnd(n1, n2)
BitAnd = Sgn(n1 And n2)
End Function

Next to your descriptions, enter

=BitAnd(n,1)
=BitAnd(n,2)
=BitAnd(n,4)
=BitAnd(n,8)
=BitAnd(n,16)
=BitAnd(n,32)
....etc

 

[Ron Rosenfeld]

Stuart Peters wrote...

Why? Spreadsheets aren't meant to work with hexadecimal numerals.
Trying to make them do so is a LOT of work.

...

Hexadecimal 'numbers' are always text, so they need to have leading
zeros in order for, say, "9" as "00009" to be less than "10000".

Assuming your table with column headings is in A1:B21, with fault code
entered in cell G3 and first fault description in cell H3, *and* with
the Analysis ToolPak loaded so that you can use HEX2DEC and DEC2HEX,
try these formulas.

H3:
=LOOKUP(RIGHT("00000"&G3,5),RIGHT("00000"&$A$2:$A$21,5),$B$2:$B$21)

H4:
=IF(IF(H3<>"",MOD(HEX2DEC($G$3),HEX2DEC(INDEX($A$2:$A$21,MATCH(H3,
$B$2:$B$21,0))))),LOOKUP(DEC2HEX(MOD(HEX2DEC($G$3),
HEX2DEC(INDEX($A$2:$A$21,MATCH(H3,$B$2:$B$21,0)))),5),
RIGHT("00000"&$A$2:$A$21,5),$B$2:$B$21),"")

Fill H4 down into H5:H22 to cover fault code entry of FFFFF.

Maybe Fill Right?
--ron

 

[Harlan Grove]

Ron Rosenfeld wrote.......

This list is vertical.

Maybe Fill Right?

How does one fill H4 *right* into H5:H22? How does filling right
produce a vertical list?

 

[Harlan Grove]

Dana DeLouis wrote...
....

I would use a custom function, something like the following.
Those values that return 1 are associated with what you are looking for.

Function BitAnd(n1, n2)
BitAnd = Sgn(n1 And n2)
End Function

....

Unnecessary. If you want to mask bits, you could use built-in
functions.

=MOD(INT(h,p),2)

returns 1 if the binary representation of h (a [decimal] number) has
the (log(p,2)-1)th bit set. Alternatively, rewrite this as

=MOD(INT(h,2^n),2)

where n = 0 to 19 to span the OP's table. When VBA is easily avoidable,
it should be avoided.

 

[Ron Rosenfeld]

Ron Rosenfeld wrote...
...

This list is vertical.


How does one fill H4 *right* into H5:H22? How does filling right
produce a vertical list?

I followed your directions incorrectly and was getting a horizontal list rather
than a vertical list.

Doing EXACTLY as you describe does give the proper results.
--ron

 

[Stuart Peters]

Harlan,
This is exactly what I was looking for (a list of all the faults given the
input), I had to expand on your formula as I go out to 8 character for the
fault.
Maybe I could have made it simple by just listing the Decimal values in my
look up table, and converting my first input.

Thanks again.

 

[C Scull]

This does not seem to be correct in excel 2003 SP2 (11.8000.6568)
INT(x) only has 1 parameter.

Dana DeLouis wrote...
....

I would use a custom function, something like the following.
Those values that return 1 are associated with what you are looking for.

Function BitAnd(n1, n2)
BitAnd = Sgn(n1 And n2)
End Function

....

Unnecessary. If you want to mask bits, you could use built-in
functions.

=MOD(INT(h,p),2)

returns 1 if the binary representation of h (a [decimal] number) has
the (log(p,2)-1)th bit set. Alternatively, rewrite this as

=MOD(INT(h,2^n),2)

where n = 0 to 19 to span the OP's table. When VBA is easily avoidable,
it should be avoided.

 

[Harlan Grove]

This does not seem to be correct in excel 2003 SP2 (11.8000.6568)
INT(x) only has 1 parameter.

"Harlan Grove" wrote: ....

....

Typo or brainlock. Meant

=MOD(INT(h/p),2)