Average by Date

[inta251 via OfficeKB.com]

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,ROW(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).

 

[Teethless mama]

example B1: blank or 0, and C1: 9
If you take D1 =C1/B1 (if B1 = 0 or blank cell it will give you #DIV/0!
error)

to trap this #DIV/0! error

D1 =IF(OR(B1="",B1=0),"",C1/B1)
or
D1 =IF(ISERROR(C1/B1),"",C1/B1)

Adjust to suit your needed

 

[T. Valko]

Try this:

Create these defined names:

Insert>Name>Define
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(rng,,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(INDEX(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]

Thanks for quick respond.
I try both ways.
Same result.

Teethless mama wrote:
example B1: blank or 0, and C1: 9
If you take D1 =C1/B1 (if B1 = 0 or blank cell it will give you #DIV/0!
error)

to trap this #DIV/0! error

D1 =IF(OR(B1="",B1=0),"",C1/B1)
or

 

[T. Valko]

Here's a refined method.

Since what you're doing is extracting the unique dates and the dates are in
ascending order:

Insert>Name>Define
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

Try this:

Create these defined names:

Insert>Name>Define
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(rng,,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(INDEX(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

 

[Ron Coderre]

Maybe something like this:

With...
On a sheet named Trips
A1: MyDate
A2:A100 dates or blanks

B1: Amount
B2:B100 amounts across from dates...or blanks

and

On sheet named Avg
A1: MyDate

Put this ARRAY FORMULA (committed with Ctrl+Shift+Enter) in
A2:
=IF(ROWS($A$2:$A2)<=SUM(--(FREQUENCY(trips!$A$2:$A$100,trips!$A$2:$A$100)>0)),SMALL(IF(ISNUMBER(trips!$A$2:$A$101),IF(ROW(trips!$A$2:$A$101)=MATCH(trips!$A$2:$A$101,trips!$A$2:$A$101,0)+1,trips!$A$2:$A$101)),ROW()-1),"")

Copy that formula down as far as you need

B1: Average

Put this regular formula in
B2:
=IF(SUMIF(trips!$A$2:$A$100,avg!$A2,trips!$B$2:$B$100),AVERAGE(trips!$B$2:INDEX(trips!$B:$B,MATCH(A2+1,trips!$A$2:$A$100,1)-1)),"")

Copy that formula down as far as you need

With your posted data, those formula return this:
MyDate Average
01/08/07
01/09/07
01/10/07
01/11/07 77.5
01/12/07
01/14/07
01/15/07 52.5
01/16/07
01/17/07 76.25
01/18/07
01/19/07
01/21/07


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

XL2002, WinXP

 

[inta251 via OfficeKB.com]

Thanks for reapply.
Before to put all this formulas to real workbook,
I decided create new workbook. Working 100% PLUS.
This is what I was looking for.

Sheet1=trips
Insert>Name>Define
Name: Rng
Refers to:

=trips!$B$2:INDEX(trips!$A$2:$A$1200,MATCH(10^10,trips!$A$2:$A$1200))

Name: Avg
Refers to:

=AVERAGE(IF(INDEX(rng,,1)>=DATE(2007,1,1),IF(INDEX(rng,,1)<=average!$A2,IF
(INDEX(rng,,2)<>"",
INDEX(rng,,2)))))

Sheet2=average

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.

BUT!!!!!!!!!
When I put all this formulas to my real workbook and did some adjustments
Sheet2 Column B STOP working as need it. I believe that because in the real
workbook data located in Column K.
What do I need adjust to correct this problem?

Thanks again for your time.
Sincerely, Igor (inta251)

 

[inta251 via OfficeKB.com]

Thanks for respond Ron.
All this formulas working fine, but with 2 problems.
Problem 1:
With my posted data I need result
MyDate Average
01/08/07 blank (because no data available yet)
01/09/07 blank (because no data available yet)
01/10/07 blank (because no data available yet)
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.

Problem 2:
If I have to calculate average from different column,
where each cell has $ amount it giving me wrong result.
Also, each cell where weekday Friday giving #N/A result.
But date and data on that weekday available.

Thanks again for your time.
Sincerely, Igor (inta251)

 

[T. Valko]

in the real workbook data located in Column K

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?

Biff

 

[Ron Coderre]

Is there anything else we should know about your situation?

***********
Regards,
Ron

XL2002, WinXP

 

[inta251 via OfficeKB.com]

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$1201,
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?

 

[inta251 via OfficeKB.com]

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$1201,
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

 

[Ron Coderre]

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

 

[T. Valko]

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$1201,
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$1200>A2,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]

With several hours of testing your formula I found calculation error.

Data from sheet 1 ‘trips’.
Col A Col K
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
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
Mon, Jan 22, 2007
Mon, Jan 22, 2007
Mon, Jan 22, 2007
Mon, Jan 22, 2007
Tue, Jan 23, 2007
Tue, Jan 23, 2007 $55.00
Wed, Jan 24, 2007
Wed, Jan 24, 2007
Wed, Jan 24, 2007
Thu, Jan 25, 2007
Thu, Jan 25, 2007
Thu, Jan 25, 2007
Fri, Jan 26, 2007
Fri, Jan 26, 2007
Fri, Jan 26, 2007
Sun, Jan 28, 2007
Sun, Jan 28, 2007
Sun, Jan 28, 2007
Mon, Jan 29, 2007
Mon, Jan 29, 2007
Mon, Jan 29, 2007
Mon, Jan 29, 2007
Mon, Jan 29, 2007
Tue, Jan 30, 2007
Tue, Jan 30, 2007
Tue, Jan 30, 2007 $27.50
Tue, Jan 30, 2007
Wed, Jan 31, 2007 $27.50
Wed, Jan 31, 2007
Wed, Jan 31, 2007


Data from sheet 2 ‘average’
Col A My formula Your formula
Mon, Jan 08, 2007 #DIV/0!
Tue, Jan 09, 2007 #DIV/0!
Wed, Jan 10, 2007 #DIV/0!
Thu, Jan 11, 2007 #DIV/0!
Fri, Jan 12, 2007 #DIV/0!
Sun, Jan 14, 2007 #DIV/0!
Mon, Jan 15, 2007 $27.50 $27.50
Tue, Jan 16, 2007 $27.50
Wed, Jan 17, 2007 $75.63 $75.63
Thu, Jan 18, 2007 $75.63
Fri, Jan 19, 2007 $75.63
Sun, Jan 21, 2007 $75.63
Mon, Jan 22, 2007 $75.63
Tue, Jan 23, 2007 $68.75 $68.75
Wed, Jan 24, 2007 $68.75
Thu, Jan 25, 2007 $68.75
Fri, Jan 26, 2007 $68.75
Sun, Jan 28, 2007 $68.75
Mon, Jan 29, 2007 $68.75
Tue, Jan 30, 2007 $58.44 $52.25 error
Wed, Jan 31, 2007 $52.25 $48.13 error
$48.13
$48.13
$48.13
$48.13

With my formula calculation correct. Only two problems as I mention above.
With your formula
=IF(OR(AND($A2<>"",ISNUMBER($B1)),
SUMIF(trips!$A$2:$A$1201,$A2,trips!$K$2:$K$1201)),
AVERAGE(trips!$K$2:INDEX(trips!$K:$K,MATCH(A2+1,trips!$A$2:$A$1201,1)-1)),"")
On January 30 error occur.
Also, I found error in row2. If cell in row2 got some amount all calculation
is wrong.

Thanks for your time.
Sincerely, Igor (inta251)

PS: Your and my calculation I checked manually.

 

[Ron Coderre]

OK....Here's the latest in a series of final solutions <vbg>

On the Average sheet

Put this regular formula in
A2:
=IF(MAX(trips!A$2:A$1200)>MAX($A$1:A1),MIN(INDEX((trips!A$2:A$1200>N(A1))*(trips!A$2:A$1200)+(trips!A$2:A$1200<=N(A1))*10^9,0)),"")

Copy that formula down as far as you need

Put this regular formula in
B2:
=IF(OR(AND($A2<>"",ISNUMBER($B1)),SUMIF(trips!$A$2:$A$1201,$A2,trips!$K$2:$K$1201)),AVERAGE(trips!$K$2:INDEX(trips!$K:$K,COUNTIF(trips!$A$2:$A$1201,"<="&($A2))+1)),"")

Copy that formula down as far as you need

Those formula return this list....

MyDate Average
01/08/07 (blank)
01/09/07 (blank)
01/10/07 (blank)
01/11/07 (blank)
01/12/07 (blank)
01/14/07 (blank)
01/15/07 27.50
01/16/07 27.50
01/17/07 75.63
01/18/07 75.63
01/19/07 75.63
01/21/07 75.63
01/22/07 75.63
01/23/07 68.75
01/24/07 68.75
01/25/07 68.75
01/26/07 68.75
01/28/07 68.75
01/29/07 68.75
01/30/07 58.44
01/31/07 52.25

Are we done yet??

***********
Regards,
Ron

XL2002, WinXP

 

[inta251 via OfficeKB.com]

BINGO!!!!
Thanks to everyone!
Special THANKS to Ron!
Easy to understand and flexible formula.
Thanks to T.Valko.
Your idea good as well, but lot off work need to be done.
Once again, THANKS to all of YOU.
Sincerely, Igor (inta251)

 

[inta251 via OfficeKB.com]

BINGO!!!!
Thanks to everyone!
Special THANKS to Ron!
Easy to understand and flexible formula.
Thanks to T.Valko.
Your idea good as well, but lot off work need to be done.
Once again, THANKS to all of YOU.
Sincerely, Igor (inta251)

 

[Ron Coderre]

Thank you so much for letting us know that worked for you, Igor. I'm glad I
could help.

***********
Regards,
Ron

XL2002, WinXP

 

[inta251 via OfficeKB.com]

Hi, Ron!
This formula wich you create for date working perfect.
{=IF(ROWS($A$2:$A2)<=SUM(--(FREQUENCY(trips!$A$2:$A$100,trips!$A$2:$A$100)>0))
,SMALL(IF(ISNUMBER(trips!$A$2:$A$101),IF(ROW(trips!$A$2:$A$101)=MATCH(trips!
$A$2:$A$101,trips!$A$2:$A$101,0)+1,trips!$A$2:$A$101)),ROW()-1),"")}

Now i have similar situation, but i need formula which working with text.
In worksheet 'trips' in column W i put Company names. Same company names
infinity.
Sample:
column W
row2 Company1
row3 Company3
row4 Company5
row5 Company1
row6 Company1
row7 Company4
row8 Company5
and so on.
Need formula in worksheet 'company' column A row 2
Sample:
column A
row2 Company1
row3 Company3
row4 Company4
row5 Company5
row6 blank till new company name
and so on.

THANKS in advence for your time.
Sincerely, Igor (inta251)