Dynamic Avarage

[Guest]

Hi,
I want a cell to display the average from a column B. Column A shows dates,
starting 20070419 and ending 20070629, and I want to display the average from
20070419 till today. and only based on filled cells.

I can solve it by using some extra columns but I would like it as easy as it
could be done.

Hopefully
ano

(Im using a Swedish version explaining why I might use the wrong terms)

 

[vezerid]

Hi,
I want a cell to display the average from a column B. Column A shows dates,
starting 20070419 and ending 20070629, and I want to display the average from
20070419 till today. and only based on filled cells.

I can solve it by using some extra columns but I would like it as easy as it
could be done.

Hopefully
ano

(Im using a Swedish version explaining why I might use the wrong terms)

If the dates are stored as text:

=SUMPRODUCT(($A$1:$A$100>="20070419")*($A$1:$A
$100<=TEXT(TODAY(),"yyyymmdd")*$B$1:$B$100)/SUMPRODUCT(($A$1:$A
$100>="20070419")*($A$1:$A$100<=TEXT(TODAY(),"yyyymmdd")*($B$1:$B
$100<>""))

If the dates are stored as numbers and formatted as yyyymmdd,

=SUMPRODUCT(($A$1:$A$100>=DATE(2007,4,19))*($A$1:$A$100<=TODAY())*$B
$1:$B$100)/SUMPRODUCT(($A$1:$A$100>="20070419")*($A$1:$A
$100<=TEXT(TODAY(),"yyyymmdd")*($B$1:$B$100<>""))

HTH
Kostis Vezerides

 

[Roger Govier]

Hi

Try
=AVERAGE(IF(AND(A1:A100<>"";A1:A100<=TEXT(TODAY();"yyyymmdd"));B1:B100))

You will need to substitute the Swedish works for Average and Text.

 

[Bob Phillips]

=AVERAGE(IF((A1:A100<>"")*(A1:A100>=--"2007-04-19")*(A1:a100<=TODAY()),B1:B100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Roger Govier]

Forget that posting.
Completely wrong - must be brain fade<g>

 

[Guest]

Your Formula works almost fine Bob. Only problem is that it counts unfilled
cells too. Some of my columns are starting blank until the first value is
entered, and I only want the average to be counted on filled cells.

(The columns shows average on a performance and not all of them are
measured from the start.)

 

[Guest]

Unfortunately I can't get your formula to work. can be some Formating
problems (we use ; instead of , for example and it might be something else
that I have missed correcting).

Ano

 

[David Biddulph]

I wonder whether, in his second formula, Kostis may have intended
=SUMPRODUCT(($A$1:$A$100>=DATE(2007,4,19))*($A$1:$A$100<=TODAY())*$B
$1:$B$100)/SUMPRODUCT(($A$1:$A$100>=DATE(2007,4,19))*($A$1:$A
$100<=TODAY())*($B$1:$B$100<>""))
rather than
=SUMPRODUCT(($A$1:$A$100>=DATE(2007,4,19))*($A$1:$A$100<=TODAY())*$B
$1:$B$100)/SUMPRODUCT(($A$1:$A$100>="20070419")*($A$1:$A
$100<=TEXT(TODAY(),"yyyymmdd")*($B$1:$B$100<>"")) ?

 

[Bob Phillips]

Do you mean the numbers are blank? If so, use

=AVERAGE(IF((A1:A100<>"")*(A1:A100>=--"2007-04-19")*(A1:A100<=TODAY())*(B1:B100<>""),B1:B100))

still an array formula

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[vezerid]

I wonder whether, in his second formula, Kostis may have intended
=SUMPRODUCT(($A$1:$A$100>=DATE(2007,4,19))*($A$1:$A$100<=TODAY())*$B
$1:$B$100)/SUMPRODUCT(($A$1:$A$100>=DATE(2007,4,19))*($A$1:$A
$100<=TODAY())*($B$1:$B$100<>""))
rather than
=SUMPRODUCT(($A$1:$A$100>=DATE(2007,4,19))*($A$1:$A$100<=TODAY())*$B
$1:$B$100)/SUMPRODUCT(($A$1:$A$100>="20070419")*($A$1:$A
$100<=TEXT(TODAY(),"yyyymmdd")*($B$1:$B$100<>"")) ?

David,
for the second time in a few days you correctly correct my hastiness!
Thank you. Indeed I copied the denominator of the first formula, for
the text case, incorrectly.

Regarding the OP, from the feedback I understand that dates are stored
as numbers. Thus Bob's last formula should be the simplest correct
solution. As for me, I still live for the day that I will not coin
unnecessarily complex solutions just b/c my mind was stuck to related
formulas at work.

Regards,
Kostis

 

[Guest]

Exactly what I wanted. Works fine, Thank You!

Do you mean the numbers are blank? If so, use

=AVERAGE(IF((A1:A100<>"")*(A1:A100>=--"2007-04-19")*(A1:A100<=TODAY())*(B1:B100<>""),B1:B100))

still an array formula

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

With the correction from David your formula worked too. Your assumption that
date was stored as numbers where also right.

Thanks for the help.

Ano