conditional format with hex2dec

[Guest]

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.

 

[Tom Ogilvy]

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

 

[Nick Hebb]

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.