Average using date criteria (Excel 97)

Topic

New Reply

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?

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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(DaysBetween),AVERAGE(DaysBetween))

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

Cheers

Reply | Quote