All,

a1:a1000 contains a list of dates including duplicates and b1:b1000 contains
amounts. I need to calculate the average amount by day of the month. For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average for the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to
get the denominator.
--
Jim

Hi

apart from the fact that i'm totally confused by your example - i think the
following formula will work for you:
=SUMPRODUCT(--(DAY($A$1:$A$3)=D10),$B$1:$B$3)/SUMPRODUCT(--(DAY($A$1:$A$3)=10))

Cheers
julieD


"JBoulton" wrote in message
...
All,

a1:a1000 contains a list of dates including duplicates and b1:b1000
contains
amounts. I need to calculate the average amount by day of the month. For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average for the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is
6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how
to
get the denominator.
--
Jim

JulieD,

I'm sorry to confuse you with the example, but it's critical to the question
at hand. Maybe I can try it again. The question is what is the average
amount attributable to each calenday day, regardless of how many items may
occur on a specific individual day? In my example, 8/10/2004 had one
transaction and 9/10/2004 had two transactions. So, the total for 9/10/2004
was 6000. That averaged with the total for 8/10/2004 of 2000 is 4000. Or,
on average the amount for the 10th day is 4000.

Your proposed solution produces 2,666.67 because the denominator calculates
to 3. I need a denominator of 2 because there are only two days=10 in the
sample data.

"JulieD" wrote:

Hi

apart from the fact that i'm totally confused by your example - i think the
following formula will work for you:
=SUMPRODUCT(--(DAY($A$1:$A$3)=D10),$B$1:$B$3)/SUMPRODUCT(--(DAY($A$1:$A$3)=10))

Cheers
julieD


"JBoulton" wrote in message
...
All,

a1:a1000 contains a list of dates including duplicates and b1:b1000
contains
amounts. I need to calculate the average amount by day of the month. For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average for the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is
6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how
to
get the denominator.
--
Jim

Copy this into D1 (watch the wrap), press ctrl + shift +
enter, and fill down:

=SUM((DAY($A$1:$A$1000)=D1)*($B$1:$B$1000))/SUM(--
(FREQUENCY(IF(DAY($A$1:$A$1000)=D1,MATCH
($A$1:$A$1000,$A$1:$A$1000,0)),MATCH
($A$1:$A$1000,$A$1:$A$1000,0))0))

HTH
Jason
Atlanta, GA

-----Original Message-----
All,

a1:a1000 contains a list of dates including duplicates
and b1:b1000 contains
amounts. I need to calculate the average amount by day
of the month. For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average for
the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10
the amount is 6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a
table) should contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10)) but
I'm stuck on how to
get the denominator.
--
Jim
.

Try...

E1, copied down:

=SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/MAX(1,SUM(IF(FREQUENCY(IF((DAY($A
$1:$A$100)=D1)*($B$1:$B$100<""),$A$1:$A$100),IF(( DAY($A$1:$A$100)=D1)*($
B$1:$B$100<""),$A$1:$A$100))0,1,0)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"JBoulton" wrote:

All,

a1:a1000 contains a list of dates including duplicates and b1:b1000 contains
amounts. I need to calculate the average amount by day of the month. For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average for the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to
get the denominator.

Jason,

Thanks for the suggestion. I tried to use frequency() as well, but couldn't
get it right. I think you may be clsoe to the solution, but the proposed
function evaluates to #N/A. I've taken it apart and found that the problem
is in the denominator (surprise.) I'll work with it a little and see if I
can figure it out following your suggestion.

"Jason Morin" wrote:

Copy this into D1 (watch the wrap), press ctrl + shift +
enter, and fill down:

=SUM((DAY($A$1:$A$1000)=D1)*($B$1:$B$1000))/SUM(--
(FREQUENCY(IF(DAY($A$1:$A$1000)=D1,MATCH
($A$1:$A$1000,$A$1:$A$1000,0)),MATCH
($A$1:$A$1000,$A$1:$A$1000,0))0))

HTH
Jason
Atlanta, GA

-----Original Message-----
All,

a1:a1000 contains a list of dates including duplicates
and b1:b1000 contains
amounts. I need to calculate the average amount by day
of the month. For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average for
the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10
the amount is 6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a
table) should contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10)) but
I'm stuck on how to
get the denominator.
--
Jim
.

The formula sums the quantity in col. B for the specific
day of the month, and then divides by the total *unique*
dates that fall on the that day of the month. 1/10/05,
1/10/05, 2/10/05, 2/10/05, 3/10/05 = 3 unique dates for
the 10th.

It works in my test. If you want a sample workbook, email
me (replace OPPOSITEOFCOLD with you know what) with the
subject line: complex count question.

HTH
Jason
Atlanta, GA

-----Original Message-----
Jason,

Thanks for the suggestion. I tried to use frequency()
as well, but couldn't
get it right. I think you may be clsoe to the solution,
but the proposed
function evaluates to #N/A. I've taken it apart and
found that the problem
is in the denominator (surprise.) I'll work with it a
little and see if I
can figure it out following your suggestion.

"Jason Morin" wrote:

Copy this into D1 (watch the wrap), press ctrl + shift
+
enter, and fill down:

=SUM((DAY($A$1:$A$1000)=D1)*($B$1:$B$1000))/SUM(--
(FREQUENCY(IF(DAY($A$1:$A$1000)=D1,MATCH
($A$1:$A$1000,$A$1:$A$1000,0)),MATCH
($A$1:$A$1000,$A$1:$A$1000,0))0))

HTH
Jason
Atlanta, GA

-----Original Message-----
All,

a1:a1000 contains a list of dates including
duplicates
and b1:b1000 contains
amounts. I need to calculate the average amount by
day
of the month. For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average
for
the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on
9/10
the amount is 6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a
table) should contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10))
but
I'm stuck on how to
get the denominator.
--
Jim
.


.

Jason,

Your function works perfectly.

Thanks for the help.

"Jason Morin" wrote:

Copy this into D1 (watch the wrap), press ctrl + shift +
enter, and fill down:

=SUM((DAY($A$1:$A$1000)=D1)*($B$1:$B$1000))/SUM(--
(FREQUENCY(IF(DAY($A$1:$A$1000)=D1,MATCH
($A$1:$A$1000,$A$1:$A$1000,0)),MATCH
($A$1:$A$1000,$A$1:$A$1000,0))0))

HTH
Jason
Atlanta, GA

-----Original Message-----
All,

a1:a1000 contains a list of dates including duplicates
and b1:b1000 contains
amounts. I need to calculate the average amount by day
of the month. For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average for
the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10
the amount is 6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a
table) should contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10)) but
I'm stuck on how to
get the denominator.
--
Jim
.

Domenic,

Perfect! Thanks for the help.

"Domenic" wrote:

Try...

E1, copied down:

=SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/MAX(1,SUM(IF(FREQUENCY(IF((DAY($A
$1:$A$100)=D1)*($B$1:$B$100<""),$A$1:$A$100),IF(( DAY($A$1:$A$100)=D1)*($
B$1:$B$100<""),$A$1:$A$100))0,1,0)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"JBoulton" wrote:

All,

a1:a1000 contains a list of dates including duplicates and b1:b1000 contains
amounts. I need to calculate the average amount by day of the month. For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average for the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to
get the denominator.

On Wed, 23 Mar 2005 07:53:06 -0800, "JBoulton"
wrote:

All,

a1:a1000 contains a list of dates including duplicates and b1:b1000 contains
amounts. I need to calculate the average amount by day of the month. For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average for the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to
get the denominator.

Try this array function. To enter an array function, after copy/pasting it
into the cell, hold <ctrl<shift while hitting <enter XL will place braces
{...} around the formula:

=IF(SUM(1*(DAY($A$1:$A$100)=D1))=0,"",
SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/SUM(--(FREQUENCY(
IF(DAY($A$1:$A$100)=D1,MATCH($A$1:$A$100,$A$1:$A$1 00,0)),
ROW(INDIRECT("1:"&ROWS($A$1:$A$100))))0)))

Note that this formula will produce a null string if there are no entries for a
particular date.


--ron

Ron,

That's a great solution, too. Thanks for the lesson.

"Ron Rosenfeld" wrote:

On Wed, 23 Mar 2005 07:53:06 -0800, "JBoulton"
wrote:

All,

a1:a1000 contains a list of dates including duplicates and b1:b1000 contains
amounts. I need to calculate the average amount by day of the month. For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average for the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to
get the denominator.

Try this array function. To enter an array function, after copy/pasting it
into the cell, hold <ctrl<shift while hitting <enter XL will place braces
{...} around the formula:

=IF(SUM(1*(DAY($A$1:$A$100)=D1))=0,"",
SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/SUM(--(FREQUENCY(
IF(DAY($A$1:$A$100)=D1,MATCH($A$1:$A$100,$A$1:$A$1 00,0)),
ROW(INDIRECT("1:"&ROWS($A$1:$A$100))))0)))

Note that this formula will produce a null string if there are no entries for a
particular date.


--ron

JBoulton,

Please note that my formula differs slightly from those provided by both
Ron and Jason. Consider the following...

8/10/04 1000
9/10/04 1500
9/10/04 1250
1/5/05 1750
1/5/05 1800
3/5/05 2250
4/5/05 2500
4/5/05 1900
4/5/05 2300
10/5/05

Fifth day of the month average:

My formula --- 4166.67

Other formulas --- 3125

As you can see, 10/5/05 is not taken into consideration until a number,
including zero, is entered in the corresponding cell in Column B.

I don't know if this makes a difference or whether this is an issue, but
I thought I'd bring it to your attention.

To get the same results as the other formulas...

=SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/MAX(1,SUM(IF(FREQUENCY(IF(DAY($A$
1:$A$100)=D1,$A$1:$A$100),IF(DAY($A$1:$A$100)=D1,$ A$1:$A$100))0,1,0)))

Hope this helps!

In article ,
"JBoulton" wrote:

Domenic,

Perfect! Thanks for the help.

"Domenic" wrote:

Try...

E1, copied down:

=SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/MAX(1,SUM(IF(FREQUENCY(IF((DAY($A
$1:$A$100)=D1)*($B$1:$B$100<""),$A$1:$A$100),IF(( DAY($A$1:$A$100)=D1)*($
B$1:$B$100<""),$A$1:$A$100))0,1,0)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"JBoulton" wrote:

All,

a1:a1000 contains a list of dates including duplicates and b1:b1000
contains
amounts. I need to calculate the average amount by day of the month.
For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average for the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is
6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should
contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how
to
get the denominator.

Ron,

Thanks for the additional information. I didn't detect the potential
problem you brought up because all of the data is in pairs, extracted from a
database. I've settled on your original solution and adapted it to the
dynamic range names in the detail worksheet. It's an elegant solution.

"JBoulton" wrote:

Ron,

That's a great solution, too. Thanks for the lesson.

"Ron Rosenfeld" wrote:

On Wed, 23 Mar 2005 07:53:06 -0800, "JBoulton"
wrote:

All,

a1:a1000 contains a list of dates including duplicates and b1:b1000 contains
amounts. I need to calculate the average amount by day of the month. For
example,

8/10/2004 2000
9/10/2004 1000
9/10/2004 5000

The total for the 10th day is 8000 and the average for the 10th day is
8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000
so the average of 2000 and 6000 is 4000.

d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain
the average by day. So, in this example, e10 = 6000.

The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to
get the denominator.

Try this array function. To enter an array function, after copy/pasting it
into the cell, hold <ctrl<shift while hitting <enter XL will place braces
{...} around the formula:

=IF(SUM(1*(DAY($A$1:$A$100)=D1))=0,"",
SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/SUM(--(FREQUENCY(
IF(DAY($A$1:$A$100)=D1,MATCH($A$1:$A$100,$A$1:$A$1 00,0)),
ROW(INDIRECT("1:"&ROWS($A$1:$A$100))))0)))

Note that this formula will produce a null string if there are no entries for a
particular date.


--ron

On Wed, 23 Mar 2005 12:07:05 -0800, "JBoulton"
wrote:

on,

Thanks for the additional information. I didn't detect the potential
problem you brought up because all of the data is in pairs, extracted from a
database. I've settled on your original solution and adapted it to the
dynamic range names in the detail worksheet. It's an elegant solution.

Thank you for the feedback.

I may have not been completely clear.

When I said "no entries for a particular date", I meant that the date itself
was missing. For example, if D1:D31 has the series 1...31, but in your data
there is no 15th of the month, then the formula next to D15 will return a null
string.

However, if there is a 15th of the month in the data, but with no value entered
next to it, the formula will assume a value of zero (0) for that date.

Since your data is in pairs, this may not be a problem. If it is, logic could
be added to test for that.


--ron