Bob,
You're right again. In my data, row 1 contains a header. Your formula
includes that in the count, thereby dividing by 3 instead of 2. The other
formula I found uses SUM and COUNT which both ignore the text in the header.
I can make yours work by subtracting 1 at the appropriate spot.
Thanks for the help today.
"Bob Phillips" wrote:
Jim,
Mine comes to 112.5 with your test data - honest!
Bob
"JBoulton" wrote in message
...
Bob,
As it turns out, that formula produces 75 with the test data - so it's not
what I was looking for after all...
"Bob Phillips" wrote:
Jim,
I get it now. Try this as an alternative
=SUM(C1:C100)/SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"JBoulton" wrote in message
...
Bob,
Here's the data again:
1/1/05 50
1/1/05 100
1/2/05 75
The total for 1/1/05 is 150 (50 + 100) and the total for 1/2/05 is 75
so
the
average for the two days is 112.50. If this was three batches of
checks,
I'd
say I spent an average of 112.50 per day.
I *have* discovered a solution on this board. This formula works:
=SUM(C:C)/COUNT(1/FREQUENCY(A:A,A:A))
Jim
"Bob Phillips" wrote:
Jim,
Sorry, I am lost. I assumed you had made a typo. I don't understand
where
the average is 112.5. 225/3 is 75, The average for the 1st is also
75.
Where
does 112.5 come from, or more to the point, how?
--
HTH
RP
(remove nothere from the email address if mailing direct)
"JBoulton" wrote in message
...
Bob,
That will certainly give me the average for any one day. What I
want
is
the
average for all the days, whether I have one item in a day or many
items
on
the same day. This data accumulates constantly. I need the daily
average.
In the first example, I'm looking for a way to calculate 112.50
(225/2)
not
75 (225/3).
Jim
"Bob Phillips" wrote:
Jim,
How about
=SUMPRODUCT(--(A1:A20=--"2005-01-01"),C1:C20)/COUNTIF(A1:A20,--"2005-01-01")
or
=SUMPRODUCT(--(A1:A2000=H1),C1:C2000)/COUNTIF(A1:A200,H1)
if the comparison date is in H1
--
HTH
RP
(remove nothere from the email address if mailing direct)
"JBoulton" wrote in message
...
Bernard,
I *could* use a pivot table to total by day and then get an
average,
but
by
the end of the year that would be a very long table. I'm
hoping
that
someone
has a single function solution.
Jim
"Bernard Liengme" wrote:
Sounds like a candidate for PIVOT Table. Read Help and come
back
with
questions.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"JBoulton" wrote in
message
...
I need a little help on a calculation.
Col A contains dates, Col C contains amounts. I need to
calculate
an
average by day given that there can be multiple entries
for
each
day.
1/1/05 50
1/1/05 100
1/2/05 75
The calc should produce 112.50
TIA
--
Jim