Return the smallest value independent of it's sign

[Ryboy30]

Hi, I hope someone is able to help me.

I am looking at quantitative data which may be anticipatory or reactive which will be represented by the direction of the values (ie positive or negative values). I need to know which result has the smallest value (it doesn't matter whether the sign is positive or negative). However, this is where it gets difficult... I could normally use the ABS function, but that changes the sign, however I still need to know if it is anticipatory or reactive (I still need to know the sign). Finally, it must also be able to consider 'zero'... ie if the particular array has the value zero then this would be the smallest.

So, for example if I have the following values:

-385, -265, 54, -145, -34, 68, -333 then the smallest value (independent of the sign is -34) and I need it to return that value.

If zero was included in the above array, then I would like it to return '0' as the smallest value.

I hope this makes sense and would appreciate any help you can provide.

regards

Ryan

 

[JoeMo]

Confirm with Ctrl + Shift + Enter

Excel Workbook
	A	B
1	-385	0
2	-265
3	54
4	-145
5	-34
6	0
7	-333
Sheet15

 

[Ryboy30]

Thanks JoeMo for your prompt reply, The formula definitely works out the minimum value quite well, but is there any way that in the solution it can retain it's sign so that I know the direction. I had the same issue whereby any time the ABS function was used it change the sign in the answer. For example if the value I needed returned was the -34 from the values above, it would change it to 34 in the answer. Unfortunately I still need to know the original sign that the value had so that I know which direction it is in. Thanks again for your help. Regards Ryan

 

[JoeMo]

Thanks JoeMo for your prompt reply, The formula definitely works out the minimum value quite well, but is there any way that in the solution it can retain it's sign so that I know the direction. I had the same issue whereby any time the ABS function was used it change the sign in the answer. For example if the value I needed returned was the -34 from the values above, it would change it to 34 in the answer. Unfortunately I still need to know the original sign that the value had so that I know which direction it is in. Thanks again for your help. Regards Ryan

Try this array formula. Note that if you have matching ABS values where only the signs differ (say -34 and 34) this function will return whichever is first in the list.

Using same layout I posted earlier:

=IF(COUNTIF(A1:A7,0)>0,0,INDEX(A1:A7,MATCH(MIN(ABS(A1:A7)),ABS(A1:A7),0)))

confirm with CSE

 

[Ryboy30]

By Joe(mo) you've done it ole boy... thanks I really appreciate it. Regards Ryan

 

[Weazel]

just a thought but since ABS doesn't return the sign couldn't you use the INT ?

essentially just =MIN(INT(A3:A9)) control shift enter

or am I wrong?



Ok, I see now...

 

[Ryboy30]

Unfortunately I have some half values etc in there... so it isn't working and is rounding them, also doesn't consider +5 to be smaller than -7. Thanks though. cheers Ryan

 

[JoeMo]

By Joe(mo) you've done it ole boy... thanks I really appreciate it. Regards Ryan

You are welcome - thanks for the reply.