| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| minimum function but want to exclude zero | Excel Discussion (Misc queries) | |||
| How to get the minimum number of one column while ignoring the err | Excel Discussion (Misc queries) | |||
| Conditional format of minimum number | Excel Worksheet Functions | |||
| excel data label format special number characters (part 2) | Excel Discussion (Misc queries) | |||
| How do I sum a number of columns and ignore the minimum value? | Excel Discussion (Misc queries) | |||
Linear Mode
