Prev: =INDEX(AF1:BD108,MATCH(Z3,AD2:AD361),AF:AR)
Next: Double quotes when using line feed
From: tcbooks on 9 Apr 2010 16:42
I have 42 rows of data and I need to average every 3rd row if ">1". I've
tried a couple formulas:

1.
averageif(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6,AN6,AQ6,">1",D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6,AN6,AQ6)

I get #VALUE.

2.
=SUM(SUMIF(INDIRECT(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6,AN6,AQ6),">1"))/SUM(COUNTIF(INDIRECT(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6,AN6,AQ6),”>1”))

The error window shows too few arguments. I found #2 on this website.

Your help would be greatly appreciated.

TC Thanks
From: Teethless mama on 9 Apr 2010 18:16
=AVERAGE(IF((MOD(COLUMN(D6:AQ6),3)=1)*(D6:AQ6>1),D6:AQ6))

ctrl+shift+enter, not just enter


"tcbooks" wrote:

> I have 42 rows of data and I need to average every 3rd row if ">1". I've
> tried a couple formulas:
>
> 1.
> averageif(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6,AN6,AQ6,">1",D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6,AN6,AQ6)
>
> I get #VALUE.
>
> 2.
> =SUM(SUMIF(INDIRECT(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6,AN6,AQ6),">1"))/SUM(COUNTIF(INDIRECT(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6,AN6,AQ6),”>1”))
>
> The error window shows too few arguments. I found #2 on this website.
>
> Your help would be greatly appreciated.
>
> TC Thanks
From: Bob Phillips on 9 Apr 2010 19:40
You mean columns not rows.

=AVERAGE(IF((MOD(COLUMN(D6:AQ6)-COLUMN(D6),3)=0)*(D6:AQ6>1),D6:AQ6))

array-entered, Ctl-Shift-Enter, not just Enter

--

HTH

Bob

"tcbooks" <tcbooks(a)discussions.microsoft.com> wrote in message
news:C8DB9062-EA68-4C37-888C-5C4708BBE656(a)microsoft.com...
>I have 42 rows of data and I need to average every 3rd row if ">1". I've
> tried a couple formulas:
>
> 1.
> averageif(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6,AN6,AQ6,">1",D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6,AN6,AQ6)
>
> I get #VALUE.
>
> 2.
> =SUM(SUMIF(INDIRECT(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6,AN6,AQ6),">1"))/SUM(COUNTIF(INDIRECT(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6,AN6,AQ6),">1"))
>
> The error window shows too few arguments. I found #2 on this website.
>
> Your help would be greatly appreciated.
>
> TC Thanks


 | 
Pages: 1
Prev: =INDEX(AF1:BD108,MATCH(Z3,AD2:AD361),AF:AR)
Next: Double quotes when using line feed