CSE problems
Posted by Colin on January 01, 2002 1:58 PM
I'm trying to use the following formula as a CSE formula or array formula and it always returns false.
What am I doing wrong?
=AVERAGE(IF(AND(A3:A28>=$F$2,A3:A28<=$G$2),B3:B28))
where f2=18 ang g2=20 and a3 through a28 falls in this range occasionally.
Posted by Aladin Akyurek on January 01, 2002 2:05 PM
Colin --
Since there is no need for an array formula to compute the average you need, use:
=(SUMIF(A3:A28,">="&F2)-SUMIF(A3:A28,">"&G2))/MAX(1,COUNTIF(A3:A28,">="&F2)-COUNTIF(A3:A28,">"&G2))
Aladin
==========
Posted by Colin on January 01, 2002 2:27 PM
Thanks for the help. Your formula didn't include averaging the B column, but I probably could have put it in somehow. However, It helped me come up with the following CSE formula. Thanks
=(SUM(IF(A3:A6000>=$F$2,B3:B6000:C3:C6000))-SUM(IF(A3:A6000>$G$2,B3:B6000:C3:C6000)))/2/(COUNT(IF(A3:A6000>=$F$2,A3:A6000))-COUNT(IF(A3:A6000>$G$2,A3:A6000)))
Posted by Aladin Akyurek on January 01, 2002 2:57 PM
Mea Culpa. I forgat all about the B-column. However, my main intend was to persuade you NOT to use an expensive array formula when not needed. Now you added a 3rd column which must also be included in the SUMIF formula:
=((SUMIF(A3:A6000,">="&F2,B3:B6000)+SUMIF(A3:A6000,">="&F2,C3:C6000))-(SUMIF(A3:A6000,">"&G2,B3:B6000)+SUMIF(A3:A6000,">"&G2,C3:C6000)))/MAX(1,COUNTIF(A3:A6000,">="&F2)-COUNTIF(A3:A6000,">"&G2))
Note. The MAX part is in order to avoid a #DIV/0! error.
Aladin
