=IF(RIGHT(A3)="1",AVERAGE($B3:INDEX($B$1:$B$15,
IF(MIN(IF(RIGHT($A4:$A$15)="1",ROW($A4:$A$15)))=0,
MAX(ROW($A$3:$A$15)),
MIN(IF(RIGHT($A4:$A$15)="1",ROW($A4:$A$15)))-1))),"")

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dastard" wrote in
message ...

yeah .. u r right , data is incorrect
lemme alter it ...
Done
check the attachement now
thanks and waiting
Regards


+-------------------------------------------------------------------+
|Filename: Test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4756 |
+-------------------------------------------------------------------+

--
Dastard
------------------------------------------------------------------------
Dastard's Profile:
http://www.excelforum.com/member.php...o&userid=34406
View this thread: http://www.excelforum.com/showthread...hreadid=541843