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

[VBA]

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

 

[Richard Schollar]

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

 

[VBA]

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

 

[Richard Schollar]

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.

 

[VBA]

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