| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MIN function
I am having trouble finding a way to create a formula that will give me the
MIN of a range that is not zero. IE - in the given range..my MIN function should give me 2 not 0. A 1 525 2 1000 3 0 4 2 5 25 Thanks! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MIN function
Daniel Q. wrote...
I am having trouble finding a way to create a formula that will give me the MIN of a range that is not zero. IE - in the given range..my MIN function should give me 2 not 0. A 1 525 2 1000 3 0 4 2 5 25 Presumably you want the smallest positive value. If so, there are many ways to do it. The basic array formula approach is =MIN(IF(A1:A50,A1:A5)) and the simplest nonarray approach is =SMALL(A1:A5,COUNTIF(A1:A5,"<=0")+1) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MIN function
=SMALL(A1:A5,COUNTIF(A1:A5,"<=0")+1)
Nice, Harlan....I'd not seen that approach before. *********** Regards, Ron XL2002, WinXP "Harlan Grove" wrote: Daniel Q. wrote... I am having trouble finding a way to create a formula that will give me the MIN of a range that is not zero. IE - in the given range..my MIN function should give me 2 not 0. A 1 525 2 1000 3 0 4 2 5 25 Presumably you want the smallest positive value. If so, there are many ways to do it. The basic array formula approach is =MIN(IF(A1:A50,A1:A5)) and the simplest nonarray approach is =SMALL(A1:A5,COUNTIF(A1:A5,"<=0")+1) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MIN function
That's awesome...thx guys
"Ron Coderre" wrote: =SMALL(A1:A5,COUNTIF(A1:A5,"<=0")+1) Nice, Harlan....I'd not seen that approach before. *********** Regards, Ron XL2002, WinXP "Harlan Grove" wrote: Daniel Q. wrote... I am having trouble finding a way to create a formula that will give me the MIN of a range that is not zero. IE - in the given range..my MIN function should give me 2 not 0. A 1 525 2 1000 3 0 4 2 5 25 Presumably you want the smallest positive value. If so, there are many ways to do it. The basic array formula approach is =MIN(IF(A1:A50,A1:A5)) and the simplest nonarray approach is =SMALL(A1:A5,COUNTIF(A1:A5,"<=0")+1) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MIN function
Try this ARRAY FORMULA*:
=MIN(IF(A1:A5<0,A1:A5)) 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 "Daniel Q." wrote: I am having trouble finding a way to create a formula that will give me the MIN of a range that is not zero. IE - in the given range..my MIN function should give me 2 not 0. A 1 525 2 1000 3 0 4 2 5 25 Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
| Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
| Date & Time | New Users to Excel | |||
| Conversion | Excel Worksheet Functions | |||
| HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Hybrid Mode
