# array function: {=min(abs(column))}

#### VBA

##### New Member
Heya

I ran into something odd today...

I have columns with data that run from row 1 to 9, then one row empty and then again 11 to 19, one row empty, etc.
The rows give (error) values in percentages and I would like to know the minimal error (closest to zero) and the maximum. The function "min" gives the minimal value over all, thus if the error is negative I get the largest negative value. To get the value closest to zero I thus need to take the absolute.

When I do this, I need to use an array function (as I take the absolute value of a whole column). This goes right a couple of times, but after 3 "rigth answers" the function only returns zero!?!
I think this might be because of the empty rows in between, but I do not understand why this goes right for the first 3...

I would really like to avoid having to create an extra column next to every now existing value, with the absolute values of the errors and then take the minimum...

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi

Your min formula presumably doesn't return the sign of the min value (closest to zero)? If you are happy for this to be the case then try:

=MIN(IF(C2:C100<>"",ABS(C2:C100)))

Confirmed with Ctrl+Shift+Enter

Yes, that is no problem - I don't need to know the sign

And thank you, it works perfectly now

I still wonder why it did not went wrong with all of the columns though, without the if-statement I mean ...

You never stated exactly what your original formulas were, so I can't tell. The blanks would return a 0 in the array formula, but that wouldn't explain why your formula worked on the first two columns.

It would be just as yours, just without the if-statement, thus {=min(abs(C2:C100))} for the case you mentioned.

Well, just let it be a mystery then - wouldn't be the first one excel presents me with

Replies
8
Views
142
Replies
4
Views
175
Replies
0
Views
335
Replies
6
Views
209
Replies
6
Views
247

1,196,513
Messages
6,015,630
Members
441,912
Latest member
Rayna_rahman00

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back