MIN Problem

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

Jan Kronsell

I have a range containing numbers the meassures differences from a standard,
fx

2
3
-6
-2
1

Now I want to find the minimum difference. If I use MIN(A1:A5) it
returns -6 in tthe above example, but actually what I want is 1. I can of
course add a column containing the absolute values and use MIN on that
column, but is there a way (function) that returns what I want?

Jan
 
Hi Jan!

Use:

=MIN(ABS(A1:A5))
Entered as an array by pressing and holding down Ctrl + Shift and then
pressing Enter.

Should appear in the formula bar as:

{=MIN(ABS(A1:A5))}

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Jan

If you actually want 1 from your examples use this array
=MIN(IF(A1:A7>=0,A1:A7))

OR

=MIN(IF(A1:A7>0,A1:A7))

However, if the range is large and/or you will be needing many, consider
the more efficient DMIN.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
I guess I should explain. If you had

2
9
-6
-1
12

and you used the ABS method, you will get 1 and i'm not sure if that is
what you would want??

In other words I'm *thinking* you want to exclude negatives rather than
make them positive.


***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
I have a range containing numbers the meassures differences from a standard,
fx

2
3
-6
-2
1

Now I want to find the minimum difference. If I use MIN(A1:A5) it
returns -6 in tthe above example, but actually what I want is 1. I can of
course add a column containing the absolute values and use MIN on that
column, but is there a way (function) that returns what I want?

Jan
Click to expand...

The array formula =MIN(ABS(A1:A5)) will return the number that has the
magnitude of the minimum difference.

If you also want the direction of that difference, then the array formula:

=INDEX(A1:A5,MATCH(TRUE,ABS(A1:A5)=MIN(ABS(A1:A5)),0))

--ron
 
Im not interested in the direction, just the size. So I*lle go for the
array formula.

Jan
 
As you can see from my other responses, what I want is the smalls diversion
from zero, no matter the direction.

Jan
 
Back
Top