On May 31, 12:51 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
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 Biddulph

"ano" wrote in message

...

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

"vezerid" wrote:

On May 29, 2:35 pm, ano wrote:
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(),"yyyy mmdd")*($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

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