AverageIF
in [General]
|
From: Very Basic User on 10 Mar 2010 11:27 I have two questions. 1. When using AVERAGEIF... actual line =AVERAGEIF(A1:A5,">2") this works great unless all values = 0 then I get #DIV/0!. I would like to have the value remain 0 (The greater than 2 is because we have a +;- 2 standard error in our insequel pull of information. How would I correct the error message? 2. When using AVERAGEIF for cells not in a range...actual line =AVERAGEIF(A1,A5,A6,">2") this formula is not acceptable at all. Any way to do this? -- Thank you for your time! John
From: Gary''s Student on 10 Mar 2010 12:05 =IF(ISERROR(AVERAGEIF(A1:A5,">2")),0,AVERAGEIF(A1:A5,">2")) -- Gary''s Student - gsnu201001 "Very Basic User" wrote: > I have two questions. > > 1. When using AVERAGEIF... actual line =AVERAGEIF(A1:A5,">2") this works > great unless all values = 0 then I get #DIV/0!. I would like to have the > value remain 0 (The greater than 2 is because we have a +;- 2 standard error > in our insequel pull of information. How would I correct the error message? > > 2. When using AVERAGEIF for cells not in a range...actual line > =AVERAGEIF(A1,A5,A6,">2") this formula is not acceptable at all. Any way to > do this? > -- > Thank you for your time! > John
From: T. Valko on 10 Mar 2010 13:23 Try these... 1: =IFERROR(AVERAGEIF(A1:A5,">2"),0) 2: no elegant way to do this one unless the non-contiguous cells follow a set pattern (every other cell, every 5th cell, every 10th cell, etc.) =SUM((A1>2)*A1,(A5>2)*A5,(A6>2)*A6)/INDEX(FREQUENCY((A1,A5:A6),2),2) With an error trap: =IFERROR(SUM((A1>2)*A1,(A5>2)*A5,(A6>2)*A6)/INDEX(FREQUENCY((A1,A5:A6),2),2),0) -- Biff Microsoft Excel MVP "Very Basic User" <VeryBasicUser(a)discussions.microsoft.com> wrote in message news:59454572-E940-40AD-BCAB-D130851D50F1(a)microsoft.com... >I have two questions. > > 1. When using AVERAGEIF... actual line =AVERAGEIF(A1:A5,">2") this works > great unless all values = 0 then I get #DIV/0!. I would like to have the > value remain 0 (The greater than 2 is because we have a +;- 2 standard > error > in our insequel pull of information. How would I correct the error > message? > > 2. When using AVERAGEIF for cells not in a range...actual line > =AVERAGEIF(A1,A5,A6,">2") this formula is not acceptable at all. Any way > to > do this? > -- > Thank you for your time! > John
From: Very Basic User on 10 Mar 2010 15:04 -- Thank you for your time! John "Gary''s Student" wrote: > =IF(ISERROR(AVERAGEIF(A1:A5,">2")),0,AVERAGEIF(A1:A5,">2")) > -- > Gary''s Student - gsnu201001 > > > "Very Basic User" wrote: > > > I have two questions. > > > > 1. When using AVERAGEIF... actual line =AVERAGEIF(A1:A5,">2") this works > > great unless all values = 0 then I get #DIV/0!. I would like to have the > > value remain 0 (The greater than 2 is because we have a +;- 2 standard error > > in our insequel pull of information. How would I correct the error message? > > > > 2. When using AVERAGEIF for cells not in a range...actual line > > =AVERAGEIF(A1,A5,A6,">2") this formula is not acceptable at all. Any way to > > do this? > > -- > > Thank you for your time! > > John
From: Very Basic User on 10 Mar 2010 15:05 Gary's student your solution was perfect for my first question. Thank you! -- Thank you for your time! John "Gary''s Student" wrote: > =IF(ISERROR(AVERAGEIF(A1:A5,">2")),0,AVERAGEIF(A1:A5,">2")) > -- > Gary''s Student - gsnu201001 > > > "Very Basic User" wrote: > > > I have two questions. > > > > 1. When using AVERAGEIF... actual line =AVERAGEIF(A1:A5,">2") this works > > great unless all values = 0 then I get #DIV/0!. I would like to have the > > value remain 0 (The greater than 2 is because we have a +;- 2 standard error > > in our insequel pull of information. How would I correct the error message? > > > > 2. When using AVERAGEIF for cells not in a range...actual line > > =AVERAGEIF(A1,A5,A6,">2") this formula is not acceptable at all. Any way to > > do this? > > -- > > Thank you for your time! > > John
|
Next
|
Last
Pages: 1 2 Prev: Counting data function Next: Microsoft Query is changing a negative number into a date |