AverageIF
in [General]
|
From: Very Basic User on 10 Mar 2010 15:07 T. Valko, thank you very much I was afraid of that. I actually started by just transfering summed cells to another location, having the code read to those cells and then hiding the columns. This is a better way to keep it clean! thank you! -- Thank you for your time! John "T. Valko" wrote: > 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: T. Valko on 10 Mar 2010 16:35 You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Very Basic User" <VeryBasicUser(a)discussions.microsoft.com> wrote in message news:92C33155-223A-4008-8A82-2FFD83B94931(a)microsoft.com... > T. Valko, thank you very much I was afraid of that. I actually started by > just transfering summed cells to another location, having the code read to > those cells and then hiding the columns. This is a better way to keep it > clean! thank you! > -- > Thank you for your time! > John > > > "T. Valko" wrote: > >> 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 >> >> >> . >>
First
|
Prev
|
Pages: 1 2 Prev: Counting data function Next: Microsoft Query is changing a negative number into a date |