Try this regular formula:

=IF(OR(AND($A2<"",ISNUMBER($B1)),SUMIF(Trips!$A$2 :$A$1201,$A2,Trips!$B$2:$B$1201)),AVERAGE(Trips!$B $2:INDEX(Trips!$B:$B,MATCH(A2+1,Trips!$A$2:$A$1201 ,1)-1)),"")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"inta251 via OfficeKB.com" wrote:

Sorry guys. Please accept my apology.
Im not PRO, that why all this confuse.
I was thinking from one formula Ill adjust for all columns which need it.
Now I see that is not so easy.
Now you know I need create average by days.
Sheet1 = trips
Column A = Days

From
Columns F to U $$$ amount, some cells will be empty.

Sheet2 = average
Column A = Days
Formula for each cell in Column A
{=IF(ROWS($A$2:$A2)<=SUM(--(FREQUENCY(trips!$A$2:$A$1200,
trips!$A$2:$A$1200)0)),SMALL(IF(ISNUMBER(trips!$A $2:$A$1201),
IF(ROW(trips!$A$2:$A$1201)=MATCH(trips!$A$2:$A$120 1,
trips!$A$2:$A$1201,0)+1,trips!$A$2:$A$1201)),ROW()-1),"")}
which generating one day from list of same days. By the way this formula
better than I had before.

Column B row 2 need formula which will be calculate average from Sheet1 =
trips Column F and so on.
Sheet1 Sheet2
Col F -------------Col B
Col G----------- Col C
And so on, till
Col U--------------Col Q

Need result as posted above
MyDate Average
01/08/07 #DIV/0!
01/09/07 #DIV/0!
01/10/07 #DIV/0!
01/11/07 $77.50
01/12/07 $77.50
01/14/07 $77.50
01/15/07 $52.50
01/16/07 $52.50
01/17/07 $76.25
01/18/07 $76.25
01/19/07 $76.25
01/21/07 $76.25
blank $76.25
blank $76.25
blank $76.25
And so on.

Once again, THANKS for your time.
Sincerely, Igor (inta251)


Currently i have formula which giving me correct result
{=AVERAGE(IF((trips!$A$2:$A$1200=DATE(2007,1,1))* (trips!$A$2:$A$1200<=$A2),
IF(trips!$K$2:$K$1200=0,"",trips!$K$2:$K$1200)))}

If possible to add something to clean error #DIV/0!
And $76.25 if no new date

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1