Jim - If you don't want to use a Pivot Table - which, with the wizard is
really easy to use, you can use the SubTotals function from the Data drop
down box. This will group the days together and then with the wizard use the
average function.
"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