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.

Actually, the formula you had wasn't too bad calculation-wise. It was just
real long!

You'll find that these formulas calculate faster than the one above:

A2:

=MIN(Trips!A2:A1200)

A3 copied down:

=IF(ROWS($1:2)<=SUM(--(FREQUENCY(Trips!A$2:A$1200,Trips!A$2:A$1200)0)), MIN(IF(Trips!A$2:A$1200A2,Trips!A$2:A$1200)),"")

As far as the average formula, since you seem to have a rather large grid of
average formulas you would want to avoid array formulas if possible. Try
Ron's latest suggestion. Just change the references from Trips column B to
Trips column F and use relative addressing for the column. Then you should
be able to copy across then down.

Biff

"inta251 via OfficeKB.com" <u30987@uwe wrote in message
news:6daf13c7f08f4@uwe...
Sorry guys. Please accept my apology.
I'm not PRO, that why all this confuse.
I was thinking from one formula I'll 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 blank (because no data available)
01/09/07 blank (because no data available)
01/10/07 blank (because no data available)
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
And so on.

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



Why didn't you tell us that to begin with?

Tell us *exactly* where your data is. Where *exactly* are the dates and
where *exactly* are the dollar amounts?

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