Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Average using date criteria (Excel 97)

    I have a spreadsheet made up of several worksheets. Each worksheet contains several thousand rows of data. The data consists of an ID Number, Date Requested, Date Paid and Days Between. I need to average this data 3 ways.

    1st - average by Days Between (did this)
    2nd - average by Days Between based on payment within the last 3 months
    3rd - average by Days Between based on payment within the last 6 months

    I know how to do date based averaging by filtering the data, but I can't figure out how to do it without filtering. Can anyone help me with the formula that I need to use?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Average using date criteria (Excel 97)

    Hi,
    Assuming the dates paid are in A2:A8 and the values to be averaged are in B2:B8, you could use something like:
    =AVERAGE(IF(TODAY()-A2:A8<=90,B2:B8,""))
    array-entered (i.e. Ctrl-Shift-Enter), which would give you the average of those paid in the last 90 days, if that's close enough?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Average using date criteria (Excel 97)

    First guess, I'd use an IF statement and the DAYS360 but I'm having trouble visualizing your spreadsheet - care to post an abbreviated version?
    =if((DAYS360(DatePaid,Now())>90,AVERAGE(DaysBetwee n),AVERAGE(DaysBetween))

    I think you need an array in there - but arrays are not my strong suit

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average using date criteria (Excel 97)

    Using your =Average formula worked. I'm going to have to look up array-entered (Ctrl-Shift-Enter). I have seen this before but have never used it and am not sure how it works, although I can tell it's a lot different than a simple Enter.

    Thanks for the help.

Posting Permissions

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