Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Minimum Value in series, excluding zero values
I am trying to find the smallest value in a series, but exclude the
cells that have zero values. For Example: A B C D E F G 2 1.1 4 0 0 0 0 I want the return value to be 1.1 (the smallest in the row excluding 0) Is this possible? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Minimum Value in series, excluding zero values
Try this ARRAY FORMULA:
=MIN(IF(A1:G1<0,A1:G1)) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: I am trying to find the smallest value in a series, but exclude the cells that have zero values. For Example: A B C D E F G 2 1.1 4 0 0 0 0 I want the return value to be 1.1 (the smallest in the row excluding 0) Is this possible? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Minimum Value in series, excluding zero values
Non array formula
=SMALL(A2:G2,COUNTIF(A2:G2,0)+1) " wrote: I am trying to find the smallest value in a series, but exclude the cells that have zero values. For Example: A B C D E F G 2 1.1 4 0 0 0 0 I want the return value to be 1.1 (the smallest in the row excluding 0) Is this possible? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Minimum Value in series, excluding zero values
I was on the fence about that formula...negative values cause incorrect
returned values. Hence, the array formula I posted. To exclude zeroes AND negative numbers, I think I would go with something like this non-array, instead: =SMALL(A2:G2,COUNTIF(A2:G2,"<=0")+1) That one returns the MIN Positive value. Otherwise, if the need is for MIN NON-ZERO value (which may be pos or neg, then maybe this? =MIN(INDEX(A2:G2+(A2:G2=0)*10^99,0)) *********** Regards, Ron XL2002, WinXP "Teethless mama" wrote: Non array formula =SMALL(A2:G2,COUNTIF(A2:G2,0)+1) " wrote: I am trying to find the smallest value in a series, but exclude the cells that have zero values. For Example: A B C D E F G 2 1.1 4 0 0 0 0 I want the return value to be 1.1 (the smallest in the row excluding 0) Is this possible? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Minimum Value in series, excluding zero values
Ron Coderre wrote...
I was on the fence about that formula...negative values cause incorrect returned values. Hence, the array formula I posted. .... If there were negative values, MIN would return the negative value with the greatest absolute value. If negative and positive values are possible, continuity means zero values should also be excluded. Most questions like this arise when there are only positive and zero values. Anyway, if you want to be safe, LARGE is just as [in]efficient as SMALL, so use either =LARGE(rng,COUNTIF(rng,"0")) or the array formula =MIN(IF(rng0,rng)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Minimum Value in series, excluding zero values
Thanks, Harlan
I like the LARGE option, even though I keep forgetting about that one...and...as I recall, you've reminded me of it before. : \ The only reason I included the min non-zero option was in case that's what was actually needed...but not asked for. *********** Best Regards, Ron XL2002, WinXP "Harlan Grove" wrote: Ron Coderre wrote... I was on the fence about that formula...negative values cause incorrect returned values. Hence, the array formula I posted. .... If there were negative values, MIN would return the negative value with the greatest absolute value. If negative and positive values are possible, continuity means zero values should also be excluded. Most questions like this arise when there are only positive and zero values. Anyway, if you want to be safe, LARGE is just as [in]efficient as SMALL, so use either =LARGE(rng,COUNTIF(rng,"0")) or the array formula =MIN(IF(rng0,rng)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find missing values in a series | Excel Worksheet Functions | |||
Return Matched Numeric Values across Rows | Excel Worksheet Functions | |||
Finding Min Cell values excluding zero in alternate columns | Excel Worksheet Functions | |||
finding values and displaying adjacent values | Excel Worksheet Functions | |||
Help needed in finding specific series via row. | Excel Worksheet Functions |