Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lowest number
I am struggling to find a formula that will allow me to find the lowest
number within a range that doesn't include either blanks or 0's in the answer. Does anyone know of a simple solution? Thanks -- Yorkie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lowest number
Try this array* formula:
=MIN(IF(A1:A9<0,A1:A9)) assuming your range of numbers occupies A1 to A9 - adjust as necessary. * As this is an array formula, then once you have typed it in (or subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) instead of just ENTER. If you do this correctly then Excel will add curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Hope this helps. Pete The Countryman wrote: I am struggling to find a formula that will allow me to find the lowest number within a range that doesn't include either blanks or 0's in the answer. Does anyone know of a simple solution? Thanks -- Yorkie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lowest number
Pete_UK wrote...
Try this array* formula: =MIN(IF(A1:A9<0,A1:A9)) .... While this array formula may be more efficient, this can be done without array formulas. =LARGE(A1:A9,COUNTIF(A1:A9,"0")) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lowest number
=SMALL(A1:A9,COUNTIF(A1:A9,0)+1)
"The Countryman" wrote: I am struggling to find a formula that will allow me to find the lowest number within a range that doesn't include either blanks or 0's in the answer. Does anyone know of a simple solution? Thanks -- Yorkie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lowest number
Won't negative numbers cause a problem with this and Teethless Mama's formula?
"Harlan Grove" wrote: Pete_UK wrote... Try this array* formula: =MIN(IF(A1:A9<0,A1:A9)) .... While this array formula may be more efficient, this can be done without array formulas. =LARGE(A1:A9,COUNTIF(A1:A9,"0")) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lowest number
JMB wrote...
Won't negative numbers cause a problem with this and Teethless Mama's formula? .... If both positive and negative numbers are possible, zeros shouldn't be excluded, and MAX and MIN already skip blank cells. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i find the lowest number in a column in Excel? | Excel Discussion (Misc queries) | |||
lowest number above 0 | Excel Discussion (Misc queries) | |||
get lowest positive number | Excel Discussion (Misc queries) | |||
Formula for displaying the lowest number of a range? | Excel Worksheet Functions | |||
Highlight lowest number | Excel Discussion (Misc queries) |