ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Dynamic Avarage (https://www.excelbanter.com/excel-worksheet-functions/144420-dynamic-avarage.html)

ano
Dynamic Avarage
 
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
Dynamic Avarage
 
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

Roger Govier
Dynamic Avarage
 
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.

--
Regards

Roger Govier


"ano" wrote in message
...
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)



Bob Phillips
Dynamic Avarage
 
=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)

"ano" wrote in message
...
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)



Roger Govier
Dynamic Avarage
 
Forget that posting.
Completely wrong - must be brain fade<g

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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.

--
Regards

Roger Govier


"ano" wrote in message
...
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)

ano
Dynamic Avarage
 
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.)


"Bob Phillips" wrote:

=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)

"ano" wrote in message
...
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)




ano
Dynamic Avarage
 
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 Biddulph[_2_]
Dynamic Avarage
 
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

Bob Phillips
Dynamic Avarage
 
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)

"ano" wrote in message
...
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.)


"Bob Phillips" wrote:

=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)

"ano" wrote in message
...
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
Dynamic Avarage
 
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

ano
Dynamic Avarage
 
Exactly what I wanted. Works fine, Thank You!

"Bob Phillips" wrote:

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)

"ano" wrote in message
...
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.)


"Bob Phillips" wrote:

=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)

"ano" wrote in message
...
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)

ano
Dynamic Avarage
 
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

"vezerid" wrote:

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


All times are GMT +1. The time now is 10:42 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com