how to sum or count values with "<" symbol in excel?

[Guest]

I need to report values as "<x" such as <0.1 , but want to assign a value of
zero when performing mathematical equations, and want to be able to count
them separately from values that don't have "<". I'm looking for syntext in
the formulas that will help me do this. Please help. Thanks!

 

[Bernie Deitrick]

While the string may look like an emoticon, you would actually use this to count them:

=COUNTIF(B:B,"*<*")

Or to create an extra column for doing the math:

=IF(ISNUMBER(FIND("<",B2)),0,B2)

copied down to match your values, then use that for averaging, etc.

HTH,
Bernie
MS Excel MVP