MIN Problem

[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

 

[Frank Kabel]

Hi
try the array entered formula (CTRL+SHIFT+ENTER)
=MIN(ABS(A1:A5))

 

[Norman Harker]

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.

 

[Jan Kronsell]

Thanks!

Never thought about using an array :-(

Jan

 

[Dave Hawley]

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 *****

 

[Dave Hawley]

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 *****

 

[Ron Rosenfeld]

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

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

 

[Jan Kronsell]

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

Jan

 

[Jan Kronsell]

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

Jan