Averages Based on Date

**guilbj2** · 09-13-2007, 11:39 AM

I'm have a sheet that uses the =TODAY() command. I'm trying to use this date as a benchmark to get a running 3 month average of some business results. Column A is a list of months (only a few months back so A1:A8) and B1:B8 is the list of results. What I'd like to do is have the formula use the current date to figure out what the prev 3 months are and get the average results. So today, it would give me average of the values for June, July and August. Opening the same sheet on Oct 2nd should provide July, August and Sept results.

**Paul** · 09-13-2007, 12:27 PM

Hi Guilbj2,

Assuming your month names (January, February, etc.) are in A1:A12 (formatted as text) and your values are in B1:B12, then in B13 (or any other cell) you can try:

Please Login or Register  to view this content.

This will lookup the month name and assign its serial number (1 through 12), then compare that with today's month. Finally, it averages the three previous months by dividing the sumproduct by 3.

Hope that helps.. and maybe someone knows a shorter route to getting the month names converted to numbers. (Of course, if you changed your spreadsheet so that you would put 1/1/07 for January, 2/1/07 for February, etc., and then formatted the cells as Custom, "mmmm", the whole LOOKUP part would go away.)