How do I find the minimum value in a range while ignoring zeros?
in [Functions]
|
Prev: Automatic Sumning of Same Cell from Different Sheets
Next: How do I use 'between' 95% and 100% for a nested IF function?
From: Ted B. on 5 Apr 2010 22:44 -- How do I find the minimum value in a range while ignoring any zeros in that range using Excel 2007?
From: T. Valko on 5 Apr 2010 22:57 If the numbers are *always* positive.. Array entered**: ..=MIN(IF(A1:A10>0,A1:A10)) Or, normally entered: =SMALL(A1:A10,COUNTIF(A1:A10,0)+1) If there might be negative numbers... Array entered**: =MIN(IF(A1:A10<>0,A1:A10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Ted B." <TedB(a)discussions.microsoft.com> wrote in message news:2B0DF041-13DB-4979-9481-9A491AB14734(a)microsoft.com... > > -- How do I find the minimum value in a range while ignoring any zeros in > that range using Excel 2007? >
From: FSt1 on 5 Apr 2010 23:21 hi try this..... =small(A1:A50,countif(A1:A50,0)+1) regards FSt1 "Ted B." wrote: > > -- How do I find the minimum value in a range while ignoring any zeros in > that range using Excel 2007? >
From: Max on 5 Apr 2010 23:22 You could use a conditional MIN, something like this in say B2, array-entered ie press CTRL+SHIFT+ENTER to confirm the formula (instead of just pressing ENTER): =MIN(IF(A2:A10>0,A2:A10)) Success? hit the YES below -- Max Singapore --- "Ted B." wrote: > -- How do I find the minimum value in a range while ignoring any zeros in > that range using Excel 2007? >
From: FSt1 on 5 Apr 2010 23:22
hi forgot to mention. adjust ranges to suit your data. Regards FSt1 "Ted B." wrote: > > -- How do I find the minimum value in a range while ignoring any zeros in > that range using Excel 2007? > |