Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hexidecimal lookup
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hexidecimal lookup
"Stuart Peters" wrote in message
... 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(). -- David Biddulph |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hexidecimal lookup
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 | +-------------------------------------------------------------------+ -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=563823 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hexidecimal lookup
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hexidecimal lookup
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 -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Stuart Peters" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hexidecimal lookup
On 21 Jul 2006 14:58:40 -0700, "Harlan Grove" wrote:
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. Maybe Fill Right? --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hexidecimal lookup
Ron Rosenfeld wrote...
On 21 Jul 2006 14:58:40 -0700, "Harlan Grove" wrote: Stuart Peters wrote... .... I should get the following list: General Fault Indicator Temperature Indicator Power Supply Indicator .... This list is vertical. 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? How does one fill H4 *right* into H5:H22? How does filling right produce a vertical list? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hexidecimal lookup
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hexidecimal lookup
On 23 Jul 2006 18:23:42 -0700, "Harlan Grove" wrote:
Ron Rosenfeld wrote... On 21 Jul 2006 14:58:40 -0700, "Harlan Grove" wrote: Stuart Peters wrote... ... I should get the following list: General Fault Indicator Temperature Indicator Power Supply Indicator ... This list is vertical. 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? 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hexidecimal lookup
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. "Harlan Grove" wrote: 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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hexidecimal lookup
This does not seem to be correct in excel 2003 SP2 (11.8000.6568)
INT(x) only has 1 parameter. "Harlan Grove" wrote: 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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hexidecimal lookup
C Scull wrote...
This does not seem to be correct in excel 2003 SP2 (11.8000.6568) INT(x) only has 1 parameter. "Harlan Grove" wrote: .... =MOD(INT(h,p),2) .... =MOD(INT(h,2^n),2) .... Typo or brainlock. Meant =MOD(INT(h/p),2) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get correct results when LOOKUP with calculated numbers | Excel Worksheet Functions | |||
Another way to lookup data | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |