filter dates by quarter

[Larry]

I’m working on a spreadsheet for my payroll, in column
“A†is a weekly pay period end date
“I†is my gross pay for that week
Holiday pay is figured on a weekly average of the last quarter gross pay.
How do I “filter†the date per quarter and then average the gross for that
quarter?

 

[Pete_UK]

In a helper column you could have a calculation which returned
something like "2007_Q4", or "2008_Q2", from the date, and thus
identify the previous quarter. There is some variability as to when
the quarters would start, so I can't give you a specific formula
unless you can give further details on this.

Hope this helps.

Pete

 

[Ron de Bruin]

Hi Larry

In my EasyFilter add-in there is a option to do this.
Maybe you like it?
http://www.rondebruin.nl/easyfilter.htm

 

[Roger Govier]

Hi Larry
In cell J14
=AVERAGE(OFFSET(I14,0,0,-13,1))
would give the average of the previous 13 weeks Gross

 

[Larry]

I’m entering my weekly pay period end date (every Saturday) in “N5 to N57â€
and through various steps I’m getting to the gross in “I5 to I57†, one sheet
52 weeks or 52 rows.
Now to figure my holiday pay, which is calculated by the previous quarter’s
weekly gross average in “I5 to I57†I need to figure out how to filter my
dates in “N5 through N57†by quarter and then average the quarterly “I5 to
I57†then put all this in at “D67†for the first quarter then repeat the
process to enter the average for the second quarter in “E67†and so on to the
4th quarter.

=AVERAGE(OFFSET(I14,0,0,-13,1)) wont work as it only filters the “gross†in
column “I†and their may be weeks of no pay entry which can overlap quarters.

1st quarter = 1/1 to 3/31
2nd quarter = 4/1 to 6/30
3rd quarter = 7/1 to 9/30
4th quarter = 10/1 to 12/31

I have a feeling that “<>†is used somewhere in the function but I’m not
sure how to get there.