In part of a col of data, I need the minimum number, but exclude z
 

In part of a row of data, I need the minimum number, but I must exclude zeros.
The zeros have to remain in place for other reasons. (the use is for golf
scores, and I am seeking the best score ever for individuals who all have
their own column of data, but have zeros when they did not play) So I
have=MIN('THIS WEEK'!C140:C211), but that formula returns zero when one or
more of those cells are a zero

In part of a col of data, I need the minimum number, but exclude z
 

Maybe

=SMALL('This week'!C140:C211,COUNTIF('This week'!C140:C211,0)+1)

Mike

"dartanion" wrote:

In part of a row of data, I need the minimum number, but I must exclude zeros.
The zeros have to remain in place for other reasons. (the use is for golf
scores, and I am seeking the best score ever for individuals who all have
their own column of data, but have zeros when they did not play) So I
have=MIN('THIS WEEK'!C140:C211), but that formula returns zero when one or
more of those cells are a zero

In part of a col of data, I need the minimum number, but exclude z
 

On second thoughts if you have negative values you could use this array
entered which stiil excludes zero

=MIN(IF('This week'!C140:C211 <0,'This week'!C140:C211))

Array entered Ctrl+Shift+Enter

Mike

"dartanion" wrote:

In part of a row of data, I need the minimum number, but I must exclude zeros.
The zeros have to remain in place for other reasons. (the use is for golf
scores, and I am seeking the best score ever for individuals who all have
their own column of data, but have zeros when they did not play) So I
have=MIN('THIS WEEK'!C140:C211), but that formula returns zero when one or
more of those cells are a zero

In part of a col of data, I need the minimum number, but exclu
 

Excel tells me there is an error in the middle of the formula and highlights
C211,COUNTIF

"Mike H" wrote:

Maybe

=SMALL('This week'!C140:C211,COUNTIF('This week'!C140:C211,0)+1)

Mike

"dartanion" wrote:

In part of a row of data, I need the minimum number, but I must exclude zeros.
The zeros have to remain in place for other reasons. (the use is for golf
scores, and I am seeking the best score ever for individuals who all have
their own column of data, but have zeros when they did not play) So I
have=MIN('THIS WEEK'!C140:C211), but that formula returns zero when one or
more of those cells are a zero

In part of a col of data, I need the minimum number, but exclu
 

Sorry but this one failed the logical tests as it returned False False False

"Mike H" wrote:

On second thoughts if you have negative values you could use this array
entered which stiil excludes zero

=MIN(IF('This week'!C140:C211 <0,'This week'!C140:C211))

Array entered Ctrl+Shift+Enter

Mike

"dartanion" wrote:

In part of a row of data, I need the minimum number, but I must exclude zeros.
The zeros have to remain in place for other reasons. (the use is for golf
scores, and I am seeking the best score ever for individuals who all have
their own column of data, but have zeros when they did not play) So I
have=MIN('THIS WEEK'!C140:C211), but that formula returns zero when one or
more of those cells are a zero

In part of a col of data, I need the minimum number, but exclu
 

Try this formula instead:
=MIN(IF('This week'!C140:C211<0,'This week'!C140:C211))
But note that this is an array formula, so you need to press
CTRL+SHIFT+ENTER rather than just ENTER when you put the formula in and
whenever you edit it.

"dartanion" wrote in message
...
Excel tells me there is an error in the middle of the formula and
highlights
C211,COUNTIF

"Mike H" wrote:

Maybe

=SMALL('This week'!C140:C211,COUNTIF('This week'!C140:C211,0)+1)

Mike

"dartanion" wrote:

In part of a row of data, I need the minimum number, but I must exclude
zeros.
The zeros have to remain in place for other reasons. (the use is for
golf
scores, and I am seeking the best score ever for individuals who all
have
their own column of data, but have zeros when they did not play) So I
have=MIN('THIS WEEK'!C140:C211), but that formula returns zero when one
or
more of those cells are a zero

In part of a col of data, I need the minimum number, but exclu
 

Hi,

I can find no error in the formula
=SMALL('This week'!C140:C211,COUNTIF('This week'!C140:C211,0)+1)
It will return the minimum positive value in the range C140:C211 on sheet
'This Week'

Like wise I can't find an error in
=MIN(IF('This week'!C140:C211 <0,'This week'!C140:C211))
Which does the same as the first formula except on the positive side can
handle negative values and on the downside is an array.

Post your data if you are still having problems.

Mike


"dartanion" wrote:

Sorry but this one failed the logical tests as it returned False False False

"Mike H" wrote:

On second thoughts if you have negative values you could use this array
entered which stiil excludes zero

=MIN(IF('This week'!C140:C211 <0,'This week'!C140:C211))

Array entered Ctrl+Shift+Enter

Mike

"dartanion" wrote:

In part of a row of data, I need the minimum number, but I must exclude zeros.
The zeros have to remain in place for other reasons. (the use is for golf
scores, and I am seeking the best score ever for individuals who all have
their own column of data, but have zeros when they did not play) So I
have=MIN('THIS WEEK'!C140:C211), but that formula returns zero when one or
more of those cells are a zero

In part of a col of data, I need the minimum number, but exclu
 

Still having no joy, maybe my fault, at the end of the column, there are
blank cells which will be filled over the coming weeks - could this be a
problem?

"Mike H" wrote:

Hi,

I can find no error in the formula
=SMALL('This week'!C140:C211,COUNTIF('This week'!C140:C211,0)+1)
It will return the minimum positive value in the range C140:C211 on sheet
'This Week'

Like wise I can't find an error in
=MIN(IF('This week'!C140:C211 <0,'This week'!C140:C211))
Which does the same as the first formula except on the positive side can
handle negative values and on the downside is an array.

Post your data if you are still having problems.

Mike


"dartanion" wrote:

Sorry but this one failed the logical tests as it returned False False False

"Mike H" wrote:

On second thoughts if you have negative values you could use this array
entered which stiil excludes zero

=MIN(IF('This week'!C140:C211 <0,'This week'!C140:C211))

Array entered Ctrl+Shift+Enter

Mike

"dartanion" wrote:

In part of a row of data, I need the minimum number, but I must exclude zeros.
The zeros have to remain in place for other reasons. (the use is for golf
scores, and I am seeking the best score ever for individuals who all have
their own column of data, but have zeros when they did not play) So I
have=MIN('THIS WEEK'!C140:C211), but that formula returns zero when one or
more of those cells are a zero

In part of a col of data, I need the minimum number, but exclu
 

Blanks in the range will not affect eother formula.

Perhaps you could post the workbook here and post the linkfor people to have
a look at.

http://www.savefile.com/

Mike

"dartanion" wrote:

Still having no joy, maybe my fault, at the end of the column, there are
blank cells which will be filled over the coming weeks - could this be a
problem?

"Mike H" wrote:

Hi,

I can find no error in the formula
=SMALL('This week'!C140:C211,COUNTIF('This week'!C140:C211,0)+1)
It will return the minimum positive value in the range C140:C211 on sheet
'This Week'

Like wise I can't find an error in
=MIN(IF('This week'!C140:C211 <0,'This week'!C140:C211))
Which does the same as the first formula except on the positive side can
handle negative values and on the downside is an array.

Post your data if you are still having problems.

Mike


"dartanion" wrote:

Sorry but this one failed the logical tests as it returned False False False

"Mike H" wrote:

On second thoughts if you have negative values you could use this array
entered which stiil excludes zero

=MIN(IF('This week'!C140:C211 <0,'This week'!C140:C211))

Array entered Ctrl+Shift+Enter

Mike

"dartanion" wrote:

In part of a row of data, I need the minimum number, but I must exclude zeros.
The zeros have to remain in place for other reasons. (the use is for golf
scores, and I am seeking the best score ever for individuals who all have
their own column of data, but have zeros when they did not play) So I
have=MIN('THIS WEEK'!C140:C211), but that formula returns zero when one or
more of those cells are a zero

In part of a col of data, I need the minimum number, but exclu
 

Thanks but no joy, maybe my fault forgot to say thet the last few cells don't
contain any data yet

"Stephen" wrote:

Try this formula instead:
=MIN(IF('This week'!C140:C211<0,'This week'!C140:C211))
But note that this is an array formula, so you need to press
CTRL+SHIFT+ENTER rather than just ENTER when you put the formula in and
whenever you edit it.

"dartanion" wrote in message
...
Excel tells me there is an error in the middle of the formula and
highlights
C211,COUNTIF

"Mike H" wrote:

Maybe

=SMALL('This week'!C140:C211,COUNTIF('This week'!C140:C211,0)+1)

Mike

"dartanion" wrote:

In part of a row of data, I need the minimum number, but I must exclude
zeros.
The zeros have to remain in place for other reasons. (the use is for
golf
scores, and I am seeking the best score ever for individuals who all
have
their own column of data, but have zeros when they did not play) So I
have=MIN('THIS WEEK'!C140:C211), but that formula returns zero when one
or
more of those cells are a zero

In part of a col of data, I need the minimum number, but exclu
 

What do you mean by "no joy"? Do you get an error value? Do you get a
numeric value that isn't the minimum? Or what?
Did you remember to array-enter the formula?
Blank cells are not a problem.
Do you definitely have a worksheet called (precisely) "This week"? (Does a
simple formula ='This week'!C140 produce a sensible result?)

What happens if you temporarily make the ranges smaller - say, C140:C150?
Then you can highlight part of the formula in the formula bar and press F9
to see the result of that part (not forgetting to use ESC to get out of this
mode). Suitable parts of the formula to highlight in this way would be:
'This week'!C140:C150
'This week'!C140:C150<0
IF('This week'!C140:C150<0,'This week'!C140:C150)

"dartanion" wrote in message
...
Thanks but no joy, maybe my fault forgot to say thet the last few cells
don't
contain any data yet

"Stephen" wrote:

Try this formula instead:
=MIN(IF('This week'!C140:C211<0,'This week'!C140:C211))
But note that this is an array formula, so you need to press
CTRL+SHIFT+ENTER rather than just ENTER when you put the formula in and
whenever you edit it.

"dartanion" wrote in message
...
Excel tells me there is an error in the middle of the formula and
highlights
C211,COUNTIF

"Mike H" wrote:

Maybe

=SMALL('This week'!C140:C211,COUNTIF('This week'!C140:C211,0)+1)

Mike

"dartanion" wrote:

In part of a row of data, I need the minimum number, but I must
exclude
zeros.
The zeros have to remain in place for other reasons. (the use is for
golf
scores, and I am seeking the best score ever for individuals who all
have
their own column of data, but have zeros when they did not play) So
I
have=MIN('THIS WEEK'!C140:C211), but that formula returns zero when
one
or
more of those cells are a zero