complex count question

[JBoulton]

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.

 

[JulieD]

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]

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.

 

[Jason Morin]

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

 

[Domenic]

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!

 

[JBoulton]

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.

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

 

[JBoulton]

Jason,

Your function works perfectly.

Thanks for the help.

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

 

[JBoulton]

Domenic,

Perfect! Thanks for the help.

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!

 

[Ron Rosenfeld]

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]

Ron,

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

 

[Domenic]

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!

 

[JBoulton]

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.

 

[Ron Rosenfeld]

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