Jan 27 2023 10:41 AM
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?
Jan 27 2023 10:49 AM
If your data is in cells A1:A6, then the formula is:
=AVERAGE(VALUE(SUBSTITUTE(A1:A6,"<","")))
Jan 27 2023 03:35 PM
@dscheikey thank you so much got the correct result. Only thing I need the answer to show that it's <
Jan 27 2023 03:57 PM
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))