Excel – absolute minimum value

I’m meddling about with some stuff in excel just now (spit), and one of the things I wanted was to find the number in a set with the minimum absolute value. That is, the value closest to zero, whether negative or positive. In 3,5,7 this would be 3, in -3,-2,5,6 it would be -2.

It’s fairly easy to get the maximum absolute value: just take max(values) and min(values) then take abs() of each of those (that makes negative numbers positive), and whichever is the biggest is what you want:


=IF(MAX(C1:C50)>ABS(MIN(C1:C50)),MAX(C1:C50),ABS(MIN(C1:C50)))

(this also puts the answer through abs() so we just get the magnitude of the value)

Doing it for minima is a good bit more complex. It turns out that the way I found was to count the values less than zero, then use small() to return the number matching that rank. (small gets the nth smallest number in the set). This was embellished a little to cope with the situation where there are only positive or negative numbers, and we get this:


=IF(MIN(C1:C50)>0,MIN(C1:C50),IF(MAX(C1:C50)<0,ABS(MAX(C1:C50)),IF(ABS(SMALL(C1:C50,COUNTIF(C1:C50,"<0")))<ABS(LARGE(C1:C50,COUNTIF(C1:C50,">0"))),ABS(SMALL(C1:C50,COUNTIF(C1:C50,"<0"))),ABS(LARGE(C1:C50,COUNTIF(C1:C50,">0"))))))

If that seems horrendously complex, that’s because it is. Welcome to the world of doing anything clever in excel. I guess it could be done more neatly with some VBA – there may even be a better way to do it in a formula so if you have it, please enlighten me in a comment.

Leave a Comment

Your email address will not be published.