Formula Question
 

For this formula

=IF(COUNTIF(A2:A19;"<2")=0;0;AVERAGE(IF(A2:A19<2;A BS(B2:B19-C2:C19))))

How would I modify so it does the calculation only when A2:A19 is
between 2 and 5 (including 2) ?

Thank you in advance.

Formula Question
 

"carl" wrote:
For this formula
=IF(COUNTIF(A2:A19;"<2")=0;0;AVERAGE(IF(A2:A19<2;A BS(B2:B19-C2:C19))))
How would I modify so it does the calculation only
when A2:A19 is between 2 and 5 (including 2) ?

Please keep all related questions in the same thread. Posting anew causes
confusion, duplicate and possible misdirection since some readers might not
see all responses.

I answered your question, even with the embellishment "including 2", in your
original thread.

I wrote....

"carl" wrote:
For this formula
=IF(COUNTIF(A2:A19,"=2")=0,0,
AVERAGE(IF(A2:A19=2,ABS(B2:B19-C2:C19))))
How would I modify so it does the calculation only when
A2:A19 is between 2 and 5 ?

First, for XL2007 and later, the original formula could simplified as
follows:

=IFERROR(AVERAGE(IF(A2:A19=2,ABS(B2:B19-C2:C19))),0)

And for your follow-up question:

=IFERROR(AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2 :B19-C2:C19))),0)

The tests "between" inclusively, including 2 and 5. Change "<=" and/or "<="
appropriately if you do not mean to include one or the other end point or
both.

The multipication behaves like a pairwise "AND" in this context. We cannot
write AND(2<=A2:A19,A2:A19<=5) because the AND() function interprets the
arrays, not the IF() function.

In XL2003 and earlier, we can write:

=IF(COUNTIF(A2:A19,"<=5")-COUNTIF(A2:A19,"<2")=0,0,
AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2:B19-C2:C19))))

That, too, tests "between" inclusively despite appearances due to the "<2".
To exclude 2, use "<=2". To exclude 5, use "<5". A little tricky.

Formula Question
 

On Jan 31, 12:53*pm, "joeu2004" wrote:
"carl" wrote:
For this formula
=IF(COUNTIF(A2:A19;"<2")=0;0;AVERAGE(IF(A2:A19<2;A BS(B2:B19-C2:C19))))
How would I modify so it does the calculation only
when A2:A19 is between 2 and 5 *(including 2) ?

Please keep all related questions in the same thread. *Posting anew causes
confusion, duplicate and possible misdirection since some readers might not
see all responses.

I answered your question, even with the embellishment "including 2", in your
original thread.

I wrote....
"carl" wrote:
For this formula
=IF(COUNTIF(A2:A19,"=2")=0,0,
AVERAGE(IF(A2:A19=2,ABS(B2:B19-C2:C19))))
How would I modify so it does the calculation only when
A2:A19 is between 2 and 5 ?

First, for XL2007 and later, the original formula could simplified as
follows:

=IFERROR(AVERAGE(IF(A2:A19=2,ABS(B2:B19-C2:C19))),0)

And for your follow-up question:

=IFERROR(AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2 :B19-C2:C19))),0)

The tests "between" inclusively, including 2 and 5. *Change "<=" and/or "<="
appropriately if you do not mean to include one or the other end point or
both.

The multipication behaves like a pairwise "AND" in this context. *We cannot
write AND(2<=A2:A19,A2:A19<=5) because the AND() function interprets the
arrays, not the IF() function.

In XL2003 and earlier, we can write:

=IF(COUNTIF(A2:A19,"<=5")-COUNTIF(A2:A19,"<2")=0,0,
AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2:B19-C2:C19))))

That, too, tests "between" inclusively despite appearances due to the "<2".
To exclude 2, use "<=2". *To exclude 5, use "<5". *A little tricky.

Thanks again and sorry about the reposting.

I'm having some trouble following the example:

In XL2003 and earlier, we can write:

=IF(COUNTIF(A2:A19,"<=5")-COUNTIF(A2:A19,"<2")=0,0,
AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2:B19-C2:C19))))

That, too, tests "between" inclusively despite appearances due to the "<2".
To exclude 2, use "<=2". To exclude 5, use "<5". A little tricky.


So if I want the formula to include 2 but exclude 5 how would it look ?

Formula Question
 

"carl" wrote:
I'm having some trouble following the example:

In XL2003 and earlier, we can write:
=IF(COUNTIF(A2:A19,"<=5")-COUNTIF(A2:A19,"<2")=0,0,
AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2:B19-C2:C19))))
That, too, tests "between" inclusively despite appearances due to the
"<2".
To exclude 2, use "<=2". To exclude 5, use "<5". A little tricky.

Sorry. My comments starting with "To exclude" were forcused on the COUNTIF
parts. I a.s.s-u-me-d the rest would be "obvious".


carl wrote:
So if I want the formula to include 2 but exclude 5 how would it look ?

=IF(COUNTIF(A2:A19,"<5")-COUNTIF(A2:A19,"<2")=0,0,
AVERAGE(IF((2<=A2:A19)*(A2:A19<5),ABS(B2:B19-C2:C19))))

Formula Question
 

On Jan 31, 1:32*pm, "joeu2004" wrote:
"carl" wrote:
I'm having some trouble following the example:

In XL2003 and earlier, we can write:
=IF(COUNTIF(A2:A19,"<=5")-COUNTIF(A2:A19,"<2")=0,0,
*AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2:B19-C2:C19))))
That, too, tests "between" inclusively despite appearances due to the
"<2".
To exclude 2, use "<=2". *To exclude 5, use "<5". *A little tricky.

Sorry. *My comments starting with "To exclude" were forcused on the COUNTIF
parts. *I a.s.s-u-me-d the rest would be "obvious".

carl wrote:
So if I want the formula to include 2 but exclude 5 how would it look ?

=IF(COUNTIF(A2:A19,"<5")-COUNTIF(A2:A19,"<2")=0,0,
*AVERAGE(IF((2<=A2:A19)*(A2:A19<5),ABS(B2:B19-C2:C19))))

Thank you. This makes sense now and works well.