| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Make a formula to calculate the avarage of baseball stats | Excel Worksheet Functions | |||
| Make dynamic charts more dynamic | Charts and Charting in Excel | |||
| Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Linear Mode
