Formula Question

[carl]

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 (including 2) ?

Thank you in advance.

 

[joeu2004]

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 (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....

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.

 

[carl]

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....


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 ?

 

[joeu2004]

I'm having some trouble following the example:

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".

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))))

 

[carl]

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".


=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.