conditional format with hex2dec
 

I want to find deviations in a data set of supposed repeating incrementing
values (00, 01, 02, ..., 253, 254, 255, 00, 01, 02 ...), in rows of 4 by 64
values, one value per cell.. I want to highlight where the incrementing
pattern is interrupted. So I do a conditional format where my Condition1 is
Formula is =B1-A1=1. That would be fine except, the gotcha is my values
aren't decimal, they are hex (00, 01, 02, ... FD, FE, FF). So I tried
Condition1 is Formula is =hex2dec(B1)-hex2dec(A1)=1.

I get this error: You may not use references to other worksheets or
workbooks for Conditional Formatting criteria.

I can do enter formulas in cells with the hex2dec(A1), but not the
conditional formatting.
thanks in advance.

conditional format with hex2dec
 

hex2dec is in the analysis toolpak which is in another workbook (an addin) -
not a native worksheet function, so the message is correct.


You might consider creating a VBA UDF which is a wrapper function for this
or which has the code to do the conversion
--
Regards,
Tom Ogilvy

"laura_in_abq" wrote in message
...
I want to find deviations in a data set of supposed repeating incrementing
values (00, 01, 02, ..., 253, 254, 255, 00, 01, 02 ...), in rows of 4 by
64
values, one value per cell.. I want to highlight where the incrementing
pattern is interrupted. So I do a conditional format where my Condition1
is
Formula is =B1-A1=1. That would be fine except, the gotcha is my values
aren't decimal, they are hex (00, 01, 02, ... FD, FE, FF). So I tried
Condition1 is Formula is =hex2dec(B1)-hex2dec(A1)=1.

I get this error: You may not use references to other worksheets or
workbooks for Conditional Formatting criteria.

I can do enter formulas in cells with the hex2dec(A1), but not the
conditional formatting.
thanks in advance.

conditional format with hex2dec
 

You can use the CODE function to get the ASCII value of the right
character, such as:

=CODE(RIGHT(B1,1))-CODE(RIGHT(A1,1))=1

There are a couple of warnings with this, though. First, it will throw
an error if either cell is empty. Second, it give a false positive if
the first character is different for the two values, e.g., EA and FB.
However, you could build on the above statement to make a complex
statement with nested If()'s.

----

Nick Hebb
BreezeTree Software
http://www.breezetree.com