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

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

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

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

Bob,

Thanks for the alternate solution. I'll play around with that one as well.

Thanks, again.

"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

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

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

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