Formula to compare to 2 other values

[ChemistB]

I will be collecting data which is in power of twos (2, 4, 8, 16, 32, etc). At some point in the collection, it will center around two numbers (e.g. 16 and 32). So, if I do a Countif, these two numbers would account for 99.9%. I want to flag via a conditional formatting formula, any value higher or lower than these two numbers. Any thoughts? Thanks

ChemistB

[jasoncw]

You could use this formula in conditional formatting (assuming cell A1):

=OR(A1<16,A1>32)

HTH

Jason

[ChemistB]

Thanks Jason

But, I don't know what the two numbers will be when I set up these templates. The first 8 numbers might be 16, then it alternates 8, 16 4 times, then a 4 or a 32 shows up. The 4 or 32 should be highlighted. Eventually however, it might turn out the two most frequent numbers are 4 and 8 as more data is added, then the 16's and 32's should highlight.

ChemistB

[MrShorty]

Use cell references instead of constants then: =Or(A1<$D$1,A1>$E$1) where D1 and E1 contain the "boundaries." If you want, you should be able to come up with worksheet formula in D1 and E1 that will automatically calculate the boundaries.

[ChemistB]

Okay, I came up with a solution using RANK, MATCH, COUNTIF and INDEX (not to mention MAX) but there has to be an easier way. In addition, this way wouldn't work if the frequency of the first two numbers is the same.

Attached is my workbook. Any way to simplify it?

Thanks
ChemistB

[ChemistB]

No ideas? :-/

ChemistB