minimum value in a range (but not null)

[D. J. Horner]

 

    I use MS Works 4.0 spreadsheet.  Can anyone tell me how to create a user function to give the MINimum value in a range, but not to include a null string ('cause it is intepreted as 0).

 

    I use the built in finction =MIN(range ref, range ref, ...).  My specifics are more like =MIN(a9:a70).  HOWEVER, some of the rows (in this example) do not have ANY entry.  I don't, therefore, want them to be considered (even as a zero).  I want them to be ignored.

 

    If there are 70 values in the list, and 1-60 have values, and 61-70 have no value (blank, or null), I want the MIN function to return the MINimum value of only rows 1-60.  But I want the function to CHECK all 1-70 anyway, just to see if there are any values in the last few rows.   

 

    Seems to me I could use a combination of several built-in functions to achieve this, but as of yet, I don't seem to be able to hit the right combinaton.

 

    ANY SUGGESTIONS???

 

---

--

        D. J. Horner - ddjkh...@msn.com

 

                "Let's see what's out there . . . . . . . ENGAGE!"

[William]

This works with Excel 2K
={MIN(IF(rng<>0,rng))}
returns the minimum if not zero in case there are negative numbers.
Array formula so enter with Cntrl,Shift,Enter

Regards

William
willw...@yahoo.com

"D. J. Horner" <ddjkh...@email.msn.com> wrote in message
news:eXkXnDr1AHA.1364@tkmsftngp02...

I use MS Works 4.0 spreadsheet. Can anyone tell me how to create a user
function to give the MINimum value in a range, but not to include a null
string ('cause it is intepreted as 0).

I use the built in finction =MIN(range ref, range ref, ...). My
specifics are more like =MIN(a9:a70). HOWEVER, some of the rows (in this
example) do not have ANY entry. I don't, therefore, want them to be
considered (even as a zero). I want them to be ignored.

If there are 70 values in the list, and 1-60 have values, and 61-70 have
no value (blank, or null), I want the MIN function to return the MINimum
value of only rows 1-60. But I want the function to CHECK all 1-70 anyway,
just to see if there are any values in the last few rows.

Seems to me I could use a combination of several built-in functions to
achieve this, but as of yet, I don't seem to be able to hit the right
combinaton.

ANY SUGGESTIONS???

----------------------------------------------------------------------------
----

[Drew Paterson]

Don't think you can do this. If you assign a name to your range and use that in your formula, MIN in Works will ignore empty cells, but as you are obviously aware not those with zeros or ' in them, so the only way I can see for you to get the MIN value would be to physically select the zero or ' cells and delete their contents first.

Check out the Works Newsgroups, however. You are more likely to find a solution there, if there is one.

Drew

"D. J. Horner" <ddjkh...@email.msn.com> wrote in message news:eXkXnDr1AHA.1364@tkmsftngp02...

I use MS Works 4.0 spreadsheet. Can anyone tell me how to create a user function to give the MINimum value in a range, but not to include a null string ('cause it is intepreted as 0).

I use the built in finction =MIN(range ref, range ref, ...). My specifics are more like =MIN(a9:a70). HOWEVER, some of the rows (in this example) do not have ANY entry. I don't, therefore, want them to be considered (even as a zero). I want them to be ignored.

If there are 70 values in the list, and 1-60 have values, and 61-70 have no value (blank, or null), I want the MIN function to return the MINimum value of only rows 1-60. But I want the function to CHECK all 1-70 anyway, just to see if there are any values in the last few rows.

Seems to me I could use a combination of several built-in functions to achieve this, but as of yet, I don't seem to be able to hit the right combinaton.

ANY SUGGESTIONS???