Workbook containing two sheets
Sheet1 = trips
Some cells in ColumnB is blank (0)
ColumnA ColumnB
Mon, Jan 08, 2007
Mon, Jan 08, 2007
Mon, Jan 08, 2007
Tue, Jan 09, 2007
Tue, Jan 09, 2007
Tue, Jan 09, 2007
Wed, Jan 10, 2007
Wed, Jan 10, 2007
Wed, Jan 10, 2007
Wed, Jan 10, 2007
Wed, Jan 10, 2007
Thu, Jan 11, 2007
Thu, Jan 11, 2007
Thu, Jan 11, 2007 $77.50
Thu, Jan 11, 2007
Fri, Jan 12, 2007
Fri, Jan 12, 2007
Sun, Jan 14, 2007
Sun, Jan 14, 2007
Sun, Jan 14, 2007
Mon, Jan 15, 2007 $27.50
Mon, Jan 15, 2007
Mon, Jan 15, 2007
Mon, Jan 15, 2007
Tue, Jan 16, 2007
Tue, Jan 16, 2007
Tue, Jan 16, 2007
Tue, Jan 16, 2007
Wed, Jan 17, 2007
Wed, Jan 17, 2007 $123.75
Wed, Jan 17, 2007
Thu, Jan 18, 2007
Thu, Jan 18, 2007
Thu, Jan 18, 2007
Fri, Jan 19, 2007
Fri, Jan 19, 2007
Sun, Jan 21, 2007
Sun, Jan 21, 2007
Sun, Jan 21, 2007
Sun, Jan 21, 2007

Sheet2 = average

Formula which generating Date from Sheet1-ColumnA as one Date in cells Sheet2-
ColumnA:
{=IF(ISNUMBER(SMALL(IF(trips!$A$3:$A$1201<trips!$ A$2:$A$1200,ROW(trips!$A$2:
$A$1200)-ROW(trips!$A$2)+1),ROW()-ROW($A$2)+1)),INDEX(trips!$A$2:$A$1200,
SMALL(IF(trips!$A$3:$A$1201<trips!$A$2:$A$1200,RO W(trips!$A$2:$A$1200)-ROW
(trips!$A$2)+1),ROW()-ROW($A$2)+1)),"")} array formula

Cells in ColumnB containing formula
{=AVERAGE(IF(trips!$A$2:$A$1200=DATE(2007,1,1),IF (trips!$A$2:$A$1200<=A2,IF
(trips!$B$2:$B$1200<"",trips!$B$2:$B$1200))))} array formula
DATE(2007,1,1) start date
A2 date in Sheet2=average ColumnA
A3
A4
............. and so on
Which is working fine (doing average by date). Only two problems in this
formula.

Problems:
1. Error message (#DIV/0!) if cells in Sheet1 ColumnB is empty.
(In this situation they empty till January,10)
2. Continuing show same last value if no date in Sheet2 ColumnA.

ColumnA ColumnB
Mon, January 08, 2007 #DIV/0!-----------Problem #1
Tue, January 09, 2007 #DIV/0!-----------Problem #1
Wed, January 10, 2007 #DIV/0!-----------Problem #1
Thu, January 11, 2007 $77.50
Fri, January 12, 2007 $77.50
Sun, January 14, 2007 $77.50
Mon, January 15, 2007 $52.50
Tue, January 16, 2007 $52.50
Wed, January 17, 2007 $76.25
Thu, January 18, 2007 $76.25
Fri, January 19, 2007 $76.25
Sun, January 21, 2007 $76.25
(blank)waiting for date from Sheet1 $76.25-----------Problem #2
(blank)waiting for date from Sheet1 $76.25-----------Problem #2
(blank)waiting for date from Sheet1 $76.25-----------Problem #2
and so on

Problem #1 need to be blank
Problem #2 need to be blank

PLEASE! ANY SUGGESTIONS.
Thanks in advance.
Sincerely, Igor (inta251).

--
Message posted via http://www.officekb.com