complex count question

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" <JBoulton@discussions.microsoft.com> wrote in message
news:91780883-77B5-4184-85D0-E2C18EF0F162@microsoft.com...
> 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" <JBoulton@discussions.microsoft.com> wrote in message
> news:91780883-77B5-4184-85D0-E2C18EF0F162@microsoft.com...
> > 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 <91780883-77B5-4184-85D0-E2C18EF0F162@microsoft.com>,
"JBoulton" <JBoulton@discussions.microsoft.com> 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
> >.
> >
>

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 <91780883-77B5-4184-85D0-E2C18EF0F162@microsoft.com>,
> "JBoulton" <JBoulton@discussions.microsoft.com> 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"
<JBoulton@discussions.microsoft.com> 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$100,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"
> <JBoulton@discussions.microsoft.com> 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$100,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 <48E1B430-0D35-4DC1-87CC-110D62142566@microsoft.com>,
"JBoulton" <JBoulton@discussions.microsoft.com> 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 <91780883-77B5-4184-85D0-E2C18EF0F162@microsoft.com>,
> > "JBoulton" <JBoulton@discussions.microsoft.com> 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"
> > <JBoulton@discussions.microsoft.com> 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$100,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"
<JBoulton@discussions.microsoft.com> 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

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
>> >.
>> >
>>
>.
>