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

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

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

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

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

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