|
filter dates by quarter
Im 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? |
filter dates by quarter
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? |
filter dates by quarter
Hi Larry
In my EasyFilter add-in there is a option to do this. Maybe you like it? http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Larry" wrote in message ... Im 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? |
filter dates by quarter
Hi Larry
In cell J14 =AVERAGE(OFFSET(I14,0,0,-13,1)) would give the average of the previous 13 weeks Gross -- Regards Roger Govier "Larry" wrote in message ... Im 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? |
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? |
| All times are GMT +1. The time now is 06:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com