MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Simple Formula - But Need Help


Posted by Simon on August 29, 2001 5:34 PM

I have a range of data I want to determine the minumum number, the MIN formula works fine but I want it to exclude the zero's and provide the all data > 0.


Posted by Riki on August 29, 2001 8:24 PM

Hi Simon,

Try this array formular:
=AVERAGE(IF(A1:A10<>0,A1:A10,FALSE))

Regards, Riki

Posted by Aladin Akyurek on August 29, 2001 11:19 PM

Riki & Simon,

Array-enter

=MIN(IF(A1:A10,A1:A10))

You need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER) to enter an array formula.

This formula excludes zero values, but allows for negative values.
If you want to exclude negative values as well, array-enter:

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

Aladin

===============

Posted by Simon on August 30, 2001 12:08 AM

Thanks Aladin - Works Perfectly

Cheers