Finding a Minimum Number other than Zero within a Range
 

Hello All, I am trying to find the minimum number in a column range.
This range contains formulas that return a positive value or a 0. I am
trying to find the minimum number other than 0. I tried to rewrite the
formula so it would return "" other than 0. This works, however it
causes the VALUE error on other cells that are linked to this range
since there are no values. Is there a way to find the minimum above
zero? Any help would be greatly apprieciated! Thank you.

Dan

Finding a Minimum Number other than Zero within a Range
 

On Apr 22, 9:06 am, cardan wrote:
Hello All, I am trying to find the minimum number in a column range.
This range contains formulas that return a positive value or a 0. I am
trying to find the minimum number other than 0. I tried to rewrite the
formula so it would return "" other than 0. This works, however it
causes the VALUE error on other cells that are linked to this range
since there are no values. Is there a way to find the minimum above
zero? Any help would be greatly apprieciated! Thank you.

Dan

Hi Dan,

This array formula seems to work on A1:A100 for example...

=MIN(IF(A1:A100<0,A1:A100))

After typing in the formula it has to be entered by pressing Ctrl +
Shift + Enter. After doing that it Excel will place it in curly
brackets.

Ken Johnson

Finding a Minimum Number other than Zero within a Range
 

Hi Cardan,

Here's a worksheet array formula to do the job:
=MIN(IF(A1:A100,A1:A10))
Simply copy the formula into your worksheet, change 'A1:A10' to your source range, and press Ctrl-Shift-Enter.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"cardan" wrote in message oups.com...
Hello All, I am trying to find the minimum number in a column range.
This range contains formulas that return a positive value or a 0. I am
trying to find the minimum number other than 0. I tried to rewrite the
formula so it would return "" other than 0. This works, however it
causes the VALUE error on other cells that are linked to this range
since there are no values. Is there a way to find the minimum above
zero? Any help would be greatly apprieciated! Thank you.

Dan

Finding a Minimum Number other than Zero within a Range
 

Cardan,

Assuming the range is A1:A100, this formula will give the smallest value and
ignores 0s and blanks.

=SMALL(A1:A100,COUNTIF(A1:A100,0)+IF(COUNTBLANK(A1 :A100)0,1,0))


--
Hope that helps.

Vergel Adriano


"cardan" wrote:

Hello All, I am trying to find the minimum number in a column range.
This range contains formulas that return a positive value or a 0. I am
trying to find the minimum number other than 0. I tried to rewrite the
formula so it would return "" other than 0. This works, however it
causes the VALUE error on other cells that are linked to this range
since there are no values. Is there a way to find the minimum above
zero? Any help would be greatly apprieciated! Thank you.

Dan

Finding a Minimum Number other than Zero within a Range
 

On Apr 21, 4:56 pm, Vergel Adriano
wrote:
Cardan,

Assuming the range is A1:A100, this formula will give the smallest value and
ignores 0s and blanks.

=SMALL(A1:A100,COUNTIF(A1:A100,0)+IF(COUNTBLANK(A1 :A100)0,1,0))

--
Hope that helps.

Vergel Adriano



"cardan" wrote:
Hello All, I am trying to find the minimum number in a column range.
This range contains formulas that return a positive value or a 0. I am
trying to find the minimum number other than 0. I tried to rewrite the
formula so it would return "" other than 0. This works, however it
causes the VALUE error on other cells that are linked to this range
since there are no values. Is there a way to find the minimum above
zero? Any help would be greatly apprieciated! Thank you.

Dan- Hide quoted text -

- Show quoted text -

Thank you for the response. I tried the formula but I still get zero.
I reformatted the columns to mine but I still get zero. I have the
cells formatted as dates, but they do equal zero so it should work. I
may want to keep away from the arrays that were suggested above. Any
suggestions? Thanks again!

Finding a Minimum Number other than Zero within a Range
 

Cardan,

I formatted column A as date. Then entered the following values, rows 1 to 5:

1/1/2007
1/1/2006
1/1/2005
0
0


Then, I entered the formula in B1 and formatted it as date. It showed
1/1/2005 which is the smallest date ignoring the 0s and blanks...

Perhaps if you could give some more details on what your data looks like and
how you're entering the formulas...



--
Hope that helps.

Vergel Adriano


"cardan" wrote:

On Apr 21, 4:56 pm, Vergel Adriano
wrote:
Cardan,

Assuming the range is A1:A100, this formula will give the smallest value and
ignores 0s and blanks.

=SMALL(A1:A100,COUNTIF(A1:A100,0)+IF(COUNTBLANK(A1 :A100)0,1,0))

--
Hope that helps.

Vergel Adriano



"cardan" wrote:
Hello All, I am trying to find the minimum number in a column range.
This range contains formulas that return a positive value or a 0. I am
trying to find the minimum number other than 0. I tried to rewrite the
formula so it would return "" other than 0. This works, however it
causes the VALUE error on other cells that are linked to this range
since there are no values. Is there a way to find the minimum above
zero? Any help would be greatly apprieciated! Thank you.

Dan- Hide quoted text -

- Show quoted text -

Thank you for the response. I tried the formula but I still get zero.
I reformatted the columns to mine but I still get zero. I have the
cells formatted as dates, but they do equal zero so it should work. I
may want to keep away from the arrays that were suggested above. Any
suggestions? Thanks again!

Finding a Minimum Number other than Zero within a Range
 

On Apr 21, 6:02 pm, Vergel Adriano
wrote:
Cardan,

I formatted column A as date. Then entered the following values, rows 1 to 5:

1/1/2007
1/1/2006
1/1/2005
0
0

Then, I entered the formula in B1 and formatted it as date. It showed
1/1/2005 which is the smallest date ignoring the 0s and blanks...

Perhaps if you could give some more details on what your data looks like and
how you're entering the formulas...

--
Hope that helps.

Vergel Adriano



"cardan" wrote:
On Apr 21, 4:56 pm, Vergel Adriano
wrote:
Cardan,

Assuming the range is A1:A100, this formula will give the smallest value and
ignores 0s and blanks.

=SMALL(A1:A100,COUNTIF(A1:A100,0)+IF(COUNTBLANK(A1 :A100)0,1,0))

--
Hope that helps.

Vergel Adriano

"cardan" wrote:
Hello All, I am trying to find the minimum number in a column range.
This range contains formulas that return a positive value or a 0. I am
trying to find the minimum number other than 0. I tried to rewrite the
formula so it would return "" other than 0. This works, however it
causes the VALUE error on other cells that are linked to this range
since there are no values. Is there a way to find the minimum above
zero? Any help would be greatly apprieciated! Thank you.

Dan- Hide quoted text -

- Show quoted text -

Thank you for the response. I tried the formula but I still get zero.
I reformatted the columns to mine but I still get zero. I have the
cells formatted as dates, but they do equal zero so it should work. I
may want to keep away from the arrays that were suggested above. Any
suggestions? Thanks again!- Hide quoted text -

- Show quoted text -

Hi Vergel, I tried your formula with just hard coded dates and still
recieved 0 or 1/0/00. In my original range, my zeros show up as
1/0/00. I don't think this should have an effect. Could I not have an
Add-on activated or something? Thanks again.

Finding a Minimum Number other than Zero within a Range
 

"cardan" wrote in message
oups.com...
Hello All, I am trying to find the minimum number in a column range.
This range contains formulas that return a positive value or a 0. I am
trying to find the minimum number other than 0. I tried to rewrite the
formula so it would return "" other than 0. This works, however it
causes the VALUE error on other cells that are linked to this range
since there are no values. Is there a way to find the minimum above
zero? Any help would be greatly apprieciated! Thank you.



what about not to search min() in original range, but in "temp" range, which
have formula for *corrected* values.
like:

=if(a1=0,10^6,a1)

assuming that 10^6 is surely greater than minimal number other than zero