How to select data in a row that is higher/lower using if problem

[Ed Harris]

Hello, I am trying to use "if" and "or" functions to select higher of lower values in a row of 40 cells. I use MAX and MIN and the mean of the row and ABS to get rid of negatives. I would like column AX to show "check" only if there is a qualifying value which is >= 0.009. It works in the AX2 but I cant work out why it is not working in AX3-5. Ideally I would like AX to show the value or the cell number of the high or low peak so that I can find it easily, any ideas?

Beam2 2021.xlsx
	AS	AT	AU	AV	AW	AX
1	mean	max	min
2	73.37322	73.3818	73.3626	0.008587	-0.01061
3	73.37163	73.37225	73.37051	0.000628	-0.00112	check
4	73.37002	73.37038	73.36972	0.00036	-0.0003	check
5	73.37266	73.37355	73.37227	0.000894	-0.00039	check
3s for 40

Cell Formulas
Range	Formula
AS2:AS5	AS2	=AVERAGE(C2:AP2)
AT2:AT5	AT2	=MAX(C2:AP2)
AU2:AU5	AU2	=MIN(C2:AP2)
AV2:AV5	AV2	=AT2-AS2
AW2:AW5	AW2	=AU2-AS2
AX2:AX5	AX2	=IF(OR(AV2 > 0.009,(ABS(AW2))<0.009),"check","")

 

[etaf]

Av2 is not greater than 0.009
Aw2 is greater than -0.009
So AV2 is NOT TRUE and so blank

 

[Ed Harris]

Thanks, It was because I put ABS on the lesser value.

Beam2 2021.xlsx
	AX
2	check
3s for 40

Cell Formulas
Range	Formula
AX2	AX2	=IF(OR(AV2 > 0.009,AW2<-0.009),"check","")

​

 

[etaf]

you are welcome
glad its resolved