Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Conditional Average Possible? - RESOLVED

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would like to average a range of numbers if the dates are between 4 weeks ago and today? any ideas.
    The dates are in column d12:d100 and the values are in column p12:p100
    any help would be greatly appreciated. Also if I can do this without VBA I would appreciate it beacuse I do not know VBA!

    As far as I know there is no averageif function.

    _________________
    mapakunk

    [ This Message was edited by: mapakunk on 2002-05-16 09:50 ]

  2. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would like to average a range of numbers if the dates are between 4 weeks ago and today? any ideas.
    The dates are in column d12:d100 and the values are in column p12:p100
    =AVERAGE(IF(D12:D100>TODAY()-28,P12:P100))


    This is an array formula and must be entered into the cell by pressing Ctrl-Shift-Enter at the same time.






    [ This Message was edited by: Steve Hartman on 2002-05-08 11:43 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-08 08:32, mapakunk wrote:
    I would like to average a range of numbers if the dates are between 4 weeks ago and today? any ideas.
    The dates are in column d12:d100 and the values are in column p12:p100
    any help would be greatly appreciated. Also if I can do this without VBA I would appreciate it beacuse I do not know VBA!

    As far as I know there is no averageif function.
    =SUMPRODUCT((D12:D100>=A1-28)*(D12:D100<=A1),P12:P100)/MAX(1,SUMPRODUCT((D12:D100>=A1-28)*(D12:D100<=A1)))

    where A1 holds the formula: =TODAY().

    Aladin

  4. #4
    New Member
    Join Date
    May 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This seems to pick the right columns, however the values this would be are about 9000 and the average that is returned is 1384.80?? I copied your formula, pasted it, deleted the space, and rpessed Ctrl+shft+enter at the end to make it an array formula maybe there is something I should be telling you that I'm not??

    Thanks for the SPEEDY reply!!!!

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    That was the ticket! Thank you very much!
    This is one to scribble down in the back of my excel2000 bible!!!!

    :>

  6. #6
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-08 08:49, mapakunk wrote:
    This seems to pick the right columns, however the values this would be are about 9000 and the average that is returned is 1384.80?? I copied your formula, pasted it, deleted the space, and rpessed Ctrl+shft+enter at the end to make it an array formula maybe there is something I should be telling you that I'm not??
    Nope, something I should have told you. Thanks to the edit function, the formula you tried was not the right one. I realised after I posted it that the "less than" should have been a "greater than" and I edited the post. Unfortunately it was after you had seen the original.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think you could also go with

    =SUMIF(D12:D100,">="&TODAY()-28,P12:P1002)/COUNTIF(D12:D100,">="&TODAY()-28)

    if you notice any performance issues.

    good luck

    edited for the cool denominator trick
    =SUMIF(D12:D100,">="&TODAY()-28,P12:P1002)/MAX(COUNTIF(D12:D100,">="&TODAY()-28),1)

    [ This Message was edited by: IML on 2002-05-08 09:43 ]

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-08 09:41, IML wrote:
    I think you could also go with

    =SUMIF(D12:D100,">="&TODAY()-28,P12:P1002)/COUNTIF(D12:D100,">="&TODAY()-28)

    if you notice any performance issues.

    good luck

    edited for the cool denominator trick
    =SUMIF(D12:D100,">="&TODAY()-28,P12:P1002)/MAX(COUNTIF(D12:D100,">="&TODAY()-28),1)

    [ This Message was edited by: IML on 2002-05-08 09:43 ]
    Makapunk,

    I'd suggest using the above SUMIF version. & it's not (just) a performance issue as Ian modestly puts. I just overlooked the fact that today is not a fixed date, so there is no need to take that as a second condition which necessitated SUMPRODUCT (you might still keep it on the back of Excel bible, though).

    Regards,

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-05-08 10:03 ]

  9. #9
    New Member
    Join Date
    May 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I guess i could use the sumproduct formula, keeping today() in A1, and then change that date if I wanted to see an average for 4 weeks from any given date.

    Thanks for the help, this BB is VERY Helpful.
    especially because it is free. Maybe someday I'll be able to return the favor...

    Tanks everybody,

    mapakunk

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •