MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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