How to average values based on time period

[Peters48]

I have a table with several columns: two of which are labeled Date and Value.
I want to extract the average from the Value column based on a date
criteria. For example, what is the average value for the last week? For the
last month? For the last year? Can I do this with DAVG and, if so, what
should the criteria be? If not, is there a formula I can use to get this
done?

 

[T. Valko]

You can use array formulas** like these.

For the week, use cells to hold to the dates for the week:

A1 = start date
B1 = end date
D110 = dates
E1:E10 = values to average

*All* of these formulas need to be array entered**.

=AVERAGE(IF(D110>=A1,IF(D110<=B1,E1:E10)))

For the month:

=AVERAGE(IF(MONTH(D110)=n,E1:E10))

Where n = the month number: Jan = 1 to Dec = 12

For the year:

=AVERAGE(IF(YEAR(D110)=n,E1:E10))

Where n = the year number: 2009, 2008, etc.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[trip_to_tokyo]

1. I reckon that this can be done through a Pivot Table.

2. I have just uploaded a file called:-

Peters48.xlsx

- to:-

www.pierrefondes.com

3. The above file will be item number 33 towards the top of the page.

I have left my rough workings in here as well (and my checks on the Pivot
Table calculations).

Pivot Table starts at cell L 1.

4. Your 2 columns of data start at cell A 9 and are highlighted in yellow.

I have only done last week and last month figures and the numbers in the
Pivot Table agree to my manual calculations.

5. When you first go into Pivot Table you will see:-

35 for January (this number checks out and is correct)

16 for December (again this number checks out and is correct).

6. If you want to see the number for last week take the following action:-

- click in cell M 2 (this has the word Jan in it)

- PivotTable Tools / Options / Group group / Group Field

- Grouping window should launch

- de-select Months (which should be highlighted)

- select Days

- change Starting at: date to read 28/12/2009 (make sure box to left NOT
ticked)

- change Ending at: date to read 03/01/2010 (make sure box to left ticked)

- change Number of days: to 7

- Hit OK.

7. Pivot Table will now change.

In cell N 2 you will get description:-

28/12/2009 – 03/01/2010

- and beneath that you will get the number 31.

This is the 31 average for last week and agrees with my manual calculation
in cell F 43.

Hope that the above has helped you out.

If it has please hit Yes.

Thanks!

 

[Peters48]

Your formula would work except the Date and Value field values are being
imported into Excel from a 3d party application and, so far, there are close
to 800 rows in the table. So, unless there's a way to convert all of them to
array values easily (i.e., not individually & manually), it would be too
time-consuming to have to constantly reformat all these imported values each
time I import the 3d party application's data.

 

[T. Valko]

unless there's a way to convert all of them to

array values easily

I don't understand what that means. Convert what to array values?

 

[Peters48]

I misunderstood your directions (the whole "array" thing has baffled me
since I started using Excel in the early '80s). Your formulas do what I
couldn't figure out how to do. Thanks for your help.

 

[T. Valko]

Ok, good deal.

See if this helps to shed some light about array formulas:

http://www.cpearson.com/Excel/ArrayFormulas.aspx