Average by Date
Here's a refined method.
Since what you're doing is extracting the unique dates and the dates are in
ascending order:
InsertNameDefine
Name: Rng
Refers to:
=Trips!$B$2:INDEX(Trips!$A$2:$A$65536,MATCH(10^10, Trips!$A$2:$A$65536))
You can use a more reasonably sized range in the name. Like A2:A1500. I'm
assuming you need a dynamic range based on how I'm "reading" your post.
Enter these formulas on Sheet2:
A1:
=SUMPRODUCT((INDEX(rng,,1)<"")/COUNTIF(INDEX(rng,,1),INDEX(rng,,1)&""))
A2: normally entered, not an array:
=MIN(INDEX(rng,,1))
A3: array entered and copied down:
=IF(ROWS($1:2)<=A$1,MIN(IF(INDEX(rng,,1)A2,INDEX( rng,,1))),"")
Format A2:An as DATE
The formula for column B is the same.
Biff
"T. Valko" wrote in message
...
Try this:
Create these defined names:
InsertNameDefine
Name: Rng
Refers to:
=Trips!$B$2:INDEX(Trips!$A$2:$A$65536,MATCH(10^10, Trips!$A$2:$A$65536))
Name: Avg
Refers to:
=AVERAGE(IF(INDEX(rng,,1)=DATE(2007,1,1),IF(INDEX (rng,,1)<=Sheet2!$A2,IF(INDEX(rng,,2)<"",INDEX(rn g,,2)))))
Enter these formulas on Sheet2:
A1:
=SUMPRODUCT((INDEX(rng,,1)<"")/COUNTIF(INDEX(rng,,1),INDEX(rng,,1)&""))
A2:
=IF(ROWS($1:1)<=A$1,INDEX(rng,SMALL(IF(FREQUENCY(I NDEX(rng,,1),INDEX(rng,,1))0,ROW(rng)-1),ROWS($1:1)),,1),"")
B2:
=IF(A2="","",IF(ISERROR(avg),"",avg))
Select both A2 and B2 and copy down as needed.
Format A2:An as DATE
Biff
"inta251 via OfficeKB.com" <u30987@uwe wrote in message
news:6d9552d226fea@uwe...
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
|