Average numbers that are entered as <

Copper Contributor

I have an excel spreadsheet that I enter lab results for the months of the year.  Most results come back from the lab as example: < 2.8  In my spreadsheet I enter the numbers like this for the month.  At the end of the year I this is in a summary of the year with an average line at the bottom.  Any numbers I enter with the < sign end up not averaging in the summary average line.  Example:  I had a row with 

< 1, < 4, < 3, 16, < 2, < 1 and my average said 16.  What is the formula I use for it to read < 4.5?

3 Replies

@Sheila_WWTP 

If your data is in cells A1:A6, then the formula is:

 

=AVERAGE(VALUE(SUBSTITUTE(A1:A6,"<","")))

 

@dscheikey thank you so much got the correct result. Only thing I need the answer to show that it's <

@Sheila_WWTP 

I do not understand exactly. A query whether the character "<" is present in the cell is achieved with the following formula. If TRUE, then the character is present. Otherwise FALSE.

=ISNUMBER(FIND(">",A1))