View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Larry Larry is offline
external usenet poster
 
Posts: 159
Default filter dates by quarter

Im entering my weekly pay period end date (every Saturday) in €œN5 to N57€
and through various steps Im 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 quarters
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 Im not
sure how to get there.


"Pete_UK" wrote:

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

On Jan 9, 3:03 pm, Larry wrote:
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?