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

[dartanion]

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

 

[Mike H]

Maybe

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

Mike

 

[Mike H]

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]

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

 

[dartanion]

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

 

[Stephen]

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.

 

[Mike H]

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]

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]

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]

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

 

[Stephen]

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)