# Thread: Average using date criteria (Excel 97)

1. ## 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. ## 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?

3. ## 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

4. ## 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
•