Take most extreme value from a set of negative and positive values.

[Hjones]

I have a set of 11 negative and positive values, I need to be able to set up a formula to select 'largest changed value' or the 'most changed value'.
I have been able to do this only with removing the signs of the numbers, which does return the correct number but I also need the associated sign.
Any ideas,
Cheers,
Helen

[DonkeyOte]

Could you elaborate by means of a sample file ? It's not clear to me at least how you're identifying the max change value etc...

[danellis]

Try setting up a column as =ABS(value), then do a =MAX(column)

dJE

[Hjones]

I have tried this and I need to know if the Max(column) value is neg or positive? This couldn't be done manually as there are 24,000 sets.

An eg of the samples:

Patient 1: 0.67452 0.00223 -0.98636

Out of the 3 values I need to take the most extreme (compared to 0), so for this case the value would be -0.98636.

Hope this clears things up?

[DonkeyOte]

So if those values were in say A1:A3 the value to be returned would be:

B1: =INDEX(A1:A3,MATCH(TRUE,ABS(A1:A3)=MAX(ABS(A1:A3)),0))
committed with CTRL + SHIFT + ENTER

B1 would return -0.98636

[squiggler47]

=if(abs(min(b3:l3))>max(b3:l3),min(b3:l3),max(b3:l3))

not sure what you want if both extremes are the same but :-

=IF(ABS(MIN(B3:L3))=MAX(B3:L3),"±","") & IF(ABS(MIN(B3:L3))>MAX(B3:L3),MIN(B3:L3),MAX(B3:L3))

will display ± value (note this is not a number its text)