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