I have fairly large amount of data, mostly in numerical form in Excel. There are occasional cells that contain non-numerical values: If certain result is below its detection limit, that cell contains a symbol "<" followed by the detection limit. For example a cell might have a non-numerical value of <0.03.
I would like to find out how to write a function that sums up a range of cells, some of which contains the symbol "<" in front of them.
I know how to write an function [ IF(LEFT(A1;1)="<";RIGHT(A1;LEN(A1)-1);A1) ] that would filter out the potential "<" sign from cell A1. But using this approach would require a new column for the calculation, and I would like to make this function work in a single cell. All the SUMIF/SUMIFS examples I have seen require that the values to be added up are in numerical form.
And I don't want to use anything like Find and Replace, because I would like to leave the original data intact.
Any ideas how to approach this problem are highly appreciated
Bookmarks