opposite of weighted average

[tom ossieur]

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

 

[David Biddulph]

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.

 

[tom ossieur]

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

 

[bj]

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)/Sum(C:C)

 

[tom ossieur]

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