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.

 

[Bigr2]

Awesome!! I will give that a try when I get to the office tomorrow. I do greatly appreciate your time and knowledge.

 

[dreid1011]

You're welcome. Let me know how it works for you.

 

[Bigr2]

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


Oops!! Thought I had typed 40 instead of 50 with that example, Although 50 should have been a nice round figure "30" for an example. Lol

 

[dreid1011]

Yeah, I was wondering about that one

 

[Bigr2]

Ok, That formula works great as long as all of the cells are filled, but if there are any blank cells throughout the range it will not display a <20 even if the data is all <20.

<20, <20, blank cell, <20 = Formula outputs average of "20" (need it to be <20)
<20, <20, <20, <20 = Formula outputs average of "<20" (correct) : )

Very impressive Excel skills, I'd never of gotten close. I don't want to be too much trouble, I can always manually enter the "<20" in the average cell for the few times a year that I might run into that situation.

 

[dreid1011]

I'll play with it. It shouldn't be much more to the formula.

 

[dreid1011]

And here it is, change ROWS( ... ) to COUNTA( ... ):

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

To

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


CTRL+SHIFT+ENTER

 

[Bigr2]

Yep... that worked!! Sweet!! Thank You Again for all the help!

 

[dreid1011]

You're welcome.

 

[Tryintouseexcel]

Hello. I am having a very similar problem. Except for me the cells that I need to be averaged are not in a nice neat range. Instead of something like B9:B39 it is B9,B19,B29,B39. How can I make the formula work?

We do 4 tests a year, so tests that have not yet been done are blank cells. And some cells (due to reporting levels) are values with < sign. When I average I want a number like <0.00020 to be treated as 0.00020. I will include a screen shot. So, for this example, how can I get an average of all 4 Cadmium cells where the blank cells are ignored and the value <0.00020 is treated like 0.00020. Thanks!