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.

 

[dreid1011]

Nvm, getting ahead of myself.

 

[Eric W]

Welcome to the MrExcel forum!

A word to the wise - it's usually better to open a new thread than stick your question on the end of a very old one. That said, try:

Book1
	A	B	C	D	E	F
1	Sample Date:	2/19/2020			2020	Averages
2	Cyanide mg/L	NA			Cyanide mg/L	0
3	Arsenic mg/L	0.04			Arsenic mg/L	0.02401
4	Cadmium mg/L	0.00034			Cadmium mg/L	0.00027
5	Chromium mg/L	0.097			Chromium mg/L	0.0504
6	Copper mg/L	0.0193			Copper mg/L	0.0342
7	Lead mg/L	0.00187			Lead mg/L	0.001305
8	Nickel mg/L	0.154			Nickel mg/L	0.080955
9	Silver mg/L	0.00084			Silver mg/L	0.00047
10	Zinc mg/L	0.0323			Zinc mg/L	0.04985
11	Sample Date:	7/22/2020
12	Cyanide mg/L	NA
13	Arsenic mg/L	0.00802
14	Cadmium mg/L	<0.00020
15	Chromium mg/L	0.0038
16	Copper mg/L	0.0491
17	Lead mg/L	0.00074
18	Nickel mg/L	0.00791
19	Silver mg/L	<0.00010
20	Zinc mg/L	0.0674
Sheet7

Cell Formulas
Range	Formula
F2:F10	F2	=AVERAGE(IF($A$2:$A$20=E2,IFERROR(SUBSTITUTE($B$2:$B$20,"<","")+0,0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tryintouseexcel]

Wow! fast replies. I am about to head home for the day, but I will try these tomorrow. And if I still can't get it I will start a brand new thread. Thanks so much!!!

 

[dreid1011]

Perhaps this:

=AVERAGE(IF($A$2:$A$36=E4,IF($B$2:$B$36<>"",IFERROR(SUBSTITUTE($B$2:$B$36,"<","")+0,0)),""))
Entered with CTRL + SHIFT + ENTER, not just ENTER.

Eric, I think you missed the blank rows in your sample.

 

[Eric W]

Eric, I think you missed the blank rows in your sample.

Yeah, true enough.

Haven't seen you here for a while!

 

[dreid1011]

Haven't seen you here for a while!

It's been a while indeed. Work has called and doesn't leave much time to play.