opposite of weighted average
 

Hi!

I have a table of data:
column A: dates
column B: values

I want to know the average value over a certain period
however, for some dates I have more values than for others, which makes that
a classic average would give a distorted picture, very much like a wighted
average, which i don't want

eg.
13-Jun-02 8250
22-May-02 8400
16-May-02 8750
15-May-02 8000
12-Apr-02 8000
28-Mar-02 8300
22-Mar-02 8250
22-Mar-02 7750
08-Mar-02 8500
01-Mar-02 8250
26-Feb-02 7500
20-Feb-02 8400
05-Feb-02 6800
05-Feb-02 7000

I understand that the solution might take more than one step as if more that
one value corresponds with 1 single day, first an average should be
calculated? or not?
is there a single formula covering this request?

thanks in advance!
tom

opposite of weighted average
 

"tom ossieur" <tom wrote in message
...
Hi!

I have a table of data:
column A: dates
column B: values

I want to know the average value over a certain period
however, for some dates I have more values than for others, which makes
that
a classic average would give a distorted picture, very much like a wighted
average, which i don't want

eg.
13-Jun-02 8250
22-May-02 8400
16-May-02 8750
15-May-02 8000
12-Apr-02 8000
28-Mar-02 8300
22-Mar-02 8250
22-Mar-02 7750
08-Mar-02 8500
01-Mar-02 8250
26-Feb-02 7500
20-Feb-02 8400
05-Feb-02 6800
05-Feb-02 7000

I understand that the solution might take more than one step as if more
that
one value corresponds with 1 single day, first an average should be
calculated? or not?
is there a single formula covering this request?

I would have thought that one option might be:
1 Take an average for each day where you have more than one value.
2 Interpolate for days where you don't have a value.
3 Average across all the days.
--
David Biddulph

opposite of weighted average
 

thanks, but there is an additional complexity, why this solution does not
help me:
table contains about 1000 data... so this involves too much work



"David Biddulph" wrote:

"tom ossieur" <tom wrote in message
...
Hi!

I have a table of data:
column A: dates
column B: values

I want to know the average value over a certain period
however, for some dates I have more values than for others, which makes
that
a classic average would give a distorted picture, very much like a wighted
average, which i don't want

eg.
13-Jun-02 8250
22-May-02 8400
16-May-02 8750
15-May-02 8000
12-Apr-02 8000
28-Mar-02 8300
22-Mar-02 8250
22-Mar-02 7750
08-Mar-02 8500
01-Mar-02 8250
26-Feb-02 7500
20-Feb-02 8400
05-Feb-02 6800
05-Feb-02 7000

I understand that the solution might take more than one step as if more
that
one value corresponds with 1 single day, first an average should be
calculated? or not?
is there a single formula covering this request?

I would have thought that one option might be:
1 Take an average for each day where you have more than one value.
2 Interpolate for days where you don't have a value.
3 Average across all the days.
--
David Biddulph

opposite of weighted average
 

one method would be to use two helper columns

if dates are in column A and data in column b starting row 1
in C1 enter =1/countif(A:A,A1)
in D1 enter =B1*C1
your average of average per day would then be = Sum(D:D)/Sum(C:C)


"tom ossieur" wrote:

Hi!

I have a table of data:
column A: dates
column B: values

I want to know the average value over a certain period
however, for some dates I have more values than for others, which makes that
a classic average would give a distorted picture, very much like a wighted
average, which i don't want

eg.
13-Jun-02 8250
22-May-02 8400
16-May-02 8750
15-May-02 8000
12-Apr-02 8000
28-Mar-02 8300
22-Mar-02 8250
22-Mar-02 7750
08-Mar-02 8500
01-Mar-02 8250
26-Feb-02 7500
20-Feb-02 8400
05-Feb-02 6800
05-Feb-02 7000

I understand that the solution might take more than one step as if more that
one value corresponds with 1 single day, first an average should be
calculated? or not?
is there a single formula covering this request?

thanks in advance!
tom

opposite of weighted average
 

Thanks, that is a useful help for step 1 of David Biddulph's solution,
however, it neglects the "empty" dates, i.e. step 2

A B C D
01 January 2000 0 1 0
02 January 2000 0 0.5 0
02 January 2000 0 0.5 0
01 December 2001 10 1 10
02 December 2001 10 1 10
03 December 2001 10 1 10
04 December 2001 10 1 10
05 December 2001 10 0.5 5
05 December 2001 10 0.5 5


Result = 7.143

Looking at the period 1Jan00 till 31Dec01, the result should be close to 5,
about 4.95 or so...

the table contains only dates for which values are available



"bj" wrote:

one method would be to use two helper columns

if dates are in column A and data in column b starting row 1
in C1 enter =1/countif(A:A,A1)
in D1 enter =B1*C1
your average of average per day would then be = Sum(D:D)/Sum(C:C)


"tom ossieur" wrote:

Hi!

I have a table of data:
column A: dates
column B: values

I want to know the average value over a certain period
however, for some dates I have more values than for others, which makes that
a classic average would give a distorted picture, very much like a wighted
average, which i don't want

eg.
13-Jun-02 8250
22-May-02 8400
16-May-02 8750
15-May-02 8000
12-Apr-02 8000
28-Mar-02 8300
22-Mar-02 8250
22-Mar-02 7750
08-Mar-02 8500
01-Mar-02 8250
26-Feb-02 7500
20-Feb-02 8400
05-Feb-02 6800
05-Feb-02 7000

I understand that the solution might take more than one step as if more that
one value corresponds with 1 single day, first an average should be
calculated? or not?
is there a single formula covering this request?

thanks in advance!
tom