HELP!! with SUBSTITUTE FUNCTION within an AVERAGE FUNTION. taking < values as the value and not as zero and then averaging within the same formula

[Bigr2]

I will try to explain my issue: I am being required to start sending a Monthly Monitoring Report to the State electronically. This report has always been done in excel and I have always reported my data in a manner where it is useful to me in other areas. The data is laboratory analysis data that is full of < values due to certain detection levels. The state report will make all of my < values (<20, <0.005) into zeros, which is what they want, however I would like to export the data to another workbook that will take all of my < values as the detection level (so <20 will need to reference 20 when averaging and not zero) for other record keeping purposes. On another report I do once every 5 years I cant use < values as zero, I have to use the detection level itself.

Ok so now the background is out of the way, If I have an entire column of <20 and other values, I want to average the entire column (skipping blank cells) with the formula seeing the <20's as 20, but visually being able to still see all the < values for reference. The closest I have come to accomplishing this is with the formula =(AVERAGE(SUBSTITUTE(RANGE OF CELLS,"<","")*1)) with CONTROL+SHIFT+ENTER to make it an array. This does exactly what I want it to do, but if there is a blank cell in the range it throws and error (#VALUE!). We do not collect data every day of the Month so it is essential that it skip the blank cells. I cannot change the formatting from the parent workbook so it will export or copy and paste to the states electronic submittal system. Any Help would be greatly appreciated.

 

[Special-K99]

=AVERAGE(IF(A1:A10<>"",SUBSTITUTE(A1:A10,"<","")*1))
Array formula, use Ctrl-Shift-Enter as you've mentioned

 

[dreid1011]

This may work for you:

=AVERAGE(IF(A1:A20<>"",SUBSTITUTE(A1:A20,"<","")+0))

CTRL+SHIFT+ENTER of course. A1:A20 being your range.

 

[Bigr2]

It WORKS!!! Thanks!!! you guys are awesome. I really appreciate it. I've got one more question though: Using the same formula, if all of my data are < values, how can I have the average display a <? So if all my data points are <20, I want the formula to equate them as 20 for averaging purposes, but still show <20 in the formulated cell for the average. It might end up being something I have to delete the formula out of and just manually enter if I ever run into that issue. Again, Thanks!!!! I no longer want to throw my computer out the window or beat my head against the wall!!

 

[dreid1011]

Display a ... what?

 

[Bigr2]

Sorry, only half of what I wrote showed up, but anyways. Thanks Again!! I was curious if using the same formula I could make the average display a < value if all the data was < values? So, if all of the data in a column are <20's, and I want the formula to see <20's as 20 for averaging purposes, I would like it to still display <20 in the formulated Average cell. It can't formulate <20 if all the data is 20, only if it's <20, If that makes sense. I know if this cant be done I can just erase the formula from the cell and manually enter the info the time or so a year i might run into this.
Thanks Again!!

 

[dreid1011]

Can you post a sample and expected result? I'm not sure I understand what you want.

 

[Bigr2]

If I have a column with a range I'm wanting to take the average of (using the same average/substitute formula you helped me with above), How can I get the that formula to display a <20 if all the data in the range are <20's, but get it to display just "20" if all the data happened to be 20 or rounded to be 20? I know a formula can be included to make an average that returned "20" be displayed as a "<20", but I could actually have an average of 20 and not <20. Not sure if that cleared up the mud much....

 

[Bigr2]

<20, <20, <20, <20 = desired displayed average "<20"
<20, <20, 20 , 20 = desired displayed average "20"
<20, <20, 21, <20 = desired displayed average "20"
20, 20 , 20 , 20= desired displayed average "20"
<20, 20, 30, 50 = desired displayed average "28"

 

[dreid1011]

I can't quite figure this one out, but I will keep trying.

Edit: And as soon as I posted this I found a solution:

=IF(COUNTIF(A1:A20,"=<20")=ROWS(A1:A20),"<20",AVERAGE(IF(A1:A20<>"",SUBSTITUTE(A1:A20,"<","")+0)))

CTRL+SHIFT+ENTER